Database Design & File Storage Servers

I'm building a web application with possibility to upload some static data and need some advices.

On the begining, I'd like to use internal disk however if data will grow I plan to use Amazon S3 as file storage,开发者_StackOverflow社区 I presume that I'll need multiple file storage containers - maybe I'll use some other CDN providers.

Also, I have 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'm not user if my approach is fair in this matter.

As far I know, I'd need to create subdomains for every separate storage container (cdn1.example.com, cdn2.example.com ... cdn15.example.com). How would you design tables for that?

My other thought was to remove storage_servers and storage_servers_files tables completely and just... create a field server in files table and then store subdomain name. Configuration then should be stored in the configuration file.

Isn't it overengineered a bit?

---

A couple of suggestions -

With S3 you don't need multiple containers or buckets just for size growth. S3 Buckets hold an unlimited number of objects (by unlimited I mean you will probably run out of items to store or money to pay before AWS runs out of space). The reason to create multiple buckets is for different application spaces or security. Using AWS IAM you can limit access to the buckets to specific applications or users.

In this case if you only have a single application with a single bucket you may want to store your security settings in configuration.

Also, in my experience over time lots of people may end up with some access to your database (developers, analysts, project managers, DBA's, etc.). Access to source control and servers is generally more limited and changes are tracked better. For this reason I prefer to keep passwords and tokens out of the DB where possible.

If you are moving to a CDN in the future you will still need a source origin for your files for the CDN to pull from.

Not sure what your ip column is for but you may wan to use DnsName instead of ip since the ip address can change a lot (especially with AWS services).

---

No it is not overengineered. Looks like your design is OK to me.