I am creating a web application with possible ways to upload some static data and want some advices.
Around the begining, Let me use internal disk if however data will grow I intend to use Amazon . com S3 as file storage, I presume that I'll need multiple file canisters - maybe I'll apply certain other CDN companies.
Also, I've following database structure:
CREATE TABLE IF NOT EXISTS `storage_servers` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NOT NULL , `ip` INT(20) NOT NULL , `access_url` LONGTEXT NULL DEFAULT NULL , // storing server access URL `username` LONGTEXT NULL DEFAULT NULL , //storing server username `password` LONGTEXT NULL DEFAULT NULL , // storing server password `token` LONGTEXT NULL DEFAULT NULL , // storing server access token, if any PRIMARY KEY (`id`) ) ENGINE = InnoDB;' CREATE TABLE IF NOT EXISTS .`storage_servers_files` ( `server_id` INT NOT NULL , `file_id` BIGINT(25) NOT NULL , INDEX `fk_servers_files_1` (`file_id` ASC) , INDEX `fk_servers_files_2` (`server_id` ASC) , CONSTRAINT `fk_servers_files_1` FOREIGN KEY (`file_id` ) REFERENCES `files` (`id` ) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `fk_servers_files_2` FOREIGN KEY (`server_id` ) REFERENCES `storage_servers` (`id` ) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;
However I am not user if my approach is fair within this matter.
As far I understand, I'd have to create subdomains for each separate storage container (cdn1.example.com, cdn2.example.com ... cdn15.example.com). How does one design tables for your?
My other thought ended up being to remove
storage_servers_files tables completely and merely... produce a area
files table after which store subdomain title. Configuration then ought to be saved within the configuration file.
Is not it overengineered a little?
A few suggestions -
With S3 you do not need multiple containers or containers only for size growth. S3 Containers hold an limitless quantity of objects (by limitless I am talking about you'll most likely exhaust products to keep or money to pay for before AWS expires of space). The main reason to produce multiple containers is perfect for different application spaces or security. Using AWS IAM you are able to limit accessibility containers to a particular programs or customers.
Within this situation should you have only just one application having a single bucket you might want to store your security configurations in configuration.
Also, in my opinion with time many individuals may finish track of some use of your database (designers, experts, project managers, DBA's, etc.). Use of source control and servers is usually more limited and changes are monitored better. Because of this I favor to help keep passwords and tokens from the DB where possible.
If you're moving to some CDN later on you still require a source origin for the files for that CDN to drag from.
Unsure what your ip column is perfect for however, you may wan to make use of DnsName rather than ip because the ip can alter a great deal (particularly with AWS services).
No it's not overengineered. Appears like your design is alright in my experience.