big db for managing user files and settings

Question

I'm about to start a project that will hopefully eventually hit milion+ users. It is some kind of a site managment. Problem is how to set the db in MySQL? Some people suggest way 1 and others way 2. I hope you can help me decide the right path to start.

needs to be stored:

1. all users info:
   user | pass | account status

2. user files:
   path to compiled htm(in user folder with uid) | http request path | file seg1 | file seg2 | file seg3

3. user settings:
   personal settings (email..) | site settings (colors..) |

my ideas to store the data are:

1. path 1: have main db with table for usernames and passwords.
   create each user with db which inside has a table for user files and a table for user settings.

2. path 2: have main db for system which inside has:
   table for users and passwords and 2 more tables for each user: user_files, user_settings.

edit, add another path:

3. path 3: 3 DB.. one for users\passwords second for user_files and third for user_settings. This way it would separate the rows' weight evenly between the user_files and user_settings. Is that true?

And would a flat file system without the DB (beside the user password) be a better option here?

thank you.

Answer 1

The first path is certainly the more conventional of the two, absent a compelling reason to break the system down into multiple schemas. Something along these lines:

CREATE TABLE user (
  user_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
  user_name VARCHAR(50) NOT NULL,
  password VARCHAR(32) NOT NULL,
  status_code INT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE user_file (
  file_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
  user_id INT NOT NULL,
  path VARCHAR(2000) NOT NULL,
  INDEX (user_id),
  CONSTRAINT FOREIGN KEY (user_id)
    REFERENCES user(user_id)
) ENGINE=InnoDB;

CREATE TABLE user_settings (
  user_id INT PRIMARY KEY NOT NULL,
  email VARCHAR(150),
  color VARCHAR(10),
  etc VARCHAR(255),
  CONSTRAINT FOREIGN KEY (user_id)
    REFERENCES user(user_id)
) ENGINE=InnoDB;