Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Hi im about to start a project that will hopefully eventually hit milion+ users which 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 have table for user files and table for user settings.

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

edit, add another path:

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

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

thank you.

share|improve this question
    
why do you believe a DB would be the way to go? why not flat files? –  Артём Царионов Mar 19 '13 at 5:18
    
thank you for asking, my self i started building all the site in flat files except the user-pass table in mysql and it is working well, but people from my work said drupal works in DB and other big CMS companies work like that and its just a better and faster way to store and retrieve data instead of flat files. so im thinking about advancing before it gets big and cant change the structure. you think doing it all in flt files, settings as ini or split, and file segs in 1 file with split or 3 files seperated is better? –  user1725378 Mar 19 '13 at 5:28
    
if your managers are forcing you to use DB then you have no other options. honestly i dont know what the best solution is, but these might help stackoverflow.com/questions/2356851/database-vs-flat-files stackoverflow.com/questions/1499239/… –  Артём Царионов Mar 19 '13 at 16:25
    
no managers here, its a side project im doing on my way home on the train every day (3hours total)... –  user1725378 Mar 19 '13 at 18:02
    
just trying to figure out what way to go to not get stuck later if i pick the wrong path. –  user1725378 Mar 19 '13 at 18:15
add comment

1 Answer

up vote 0 down vote accepted

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;
share|improve this answer
    
thank you for your answer. i was suggested a third path which i think would split the db better. 3 DB.. one for users\passwords second for user_files third for user_settings that way it would separate the rows weight evenly between the user_files and user_settings. is that true? –  user1725378 Mar 19 '13 at 4:48
    
Three separate databases? No, I wouldn't do that. I would just use the three tables above in a single database. –  mdoyle Mar 19 '13 at 13:14
    
Its not 3 tables, its (2xUsersCount)+1 tables. Is it still preffered? –  user1725378 Mar 19 '13 at 13:31
    
Per-user tables? So adding a new user means adding two new tables? –  mdoyle Mar 19 '13 at 13:51
    
Yes each user needs a files table and a settings table –  user1725378 Mar 19 '13 at 14:40
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.