CODEWITHSUNDEEP
mysql
Ord
Ord

Reputation: 5843

Multiple tables or one mega-table in SQL?

I am new to MySQL. I am trying to design an online file storage system. The actual file storage will be taken care of separately - I just want to use MySQL to make a database of file metadata (eg. file names, file sizes, file types, permissions, etc.). My system will need to handle up to 1000 users, who will each be able to store up to 1000 files.

I want to create a database of file records. Is is best to create one file-record table per user, and then make another table which lists the users and references the corresponding tables? Or should I make one big table of file records, and then simply include a column called "user"?

I guess the advantage of the multiple table approach is that I never have to step through 1,000,000 records. But I don't know much about mySQL, so I don't know if 1,000,000 records is a lot to work through or not.

Upvotes: 5

Views: 7302

Answers (5)

Alnitak
Alnitak

Reputation: 339786

I'd go for all the file records in one table. 1 million records isn't really all that big a deal for MySQL - I've used tables with 100x more records than that without any problem.

Just make sure your indexes are good. Also consider using the InnoDB engine instead of MyISAM although there'll be a trade off between the performance lost from using InnoDB against the benefit of getting row-level locking.

The advantages of the one table vs many tables are being to do single SQL queries such as:

  • what's the total size of all of the files in the system:

    SELECT SUM(size) FROM files

  • show me the top N users by file size (or count):

    SELECT user, SUM(size) AS total FROM files GROUP BY user ORDER BY total DESC LIMIT 10

  • show me the last N modified files:

    SELECT * FROM files ORDER BY mtime DESC LIMIT 10

All of these are impossible to implement efficiently if you have a separate table per user.

Upvotes: 5

Abhay
Abhay

Reputation: 6645

For sure, one large table; MySQL is powerful and efficient and fast and it won't mind a million records. With a single table, you can write more simpler queries to retrieve any data, like:

  • what is the total number of files or per user
  • what is the total number of files per file type
  • what is the average file size
  • amount of file size consumed per user

Make sure that your indexes are rightly created, which will depend on the types of queries you intend to run.

Also, if there won't be very frequent writes to your tables, I'd suggest you go for MyISAM storage engine because of its higher performance. However, if contrary, go for InnoDB that supports row-level locking and therefore fewer locking conflicts.

Hope you'll soon experience how fun MySQL is :-)

Upvotes: 1

Shef
Shef

Reputation: 45589

It's best to have multiple tables, because this way you avoid the bottleneck of table locks.

Just to get you started I am giving you three tables:

users: user_id(PK) | name | surname | ...

files: file_id(PK) | file_name | file_extension | upload_date | ...

users_files: (user_id | file_id )(PK)

Notice: users_files should have compound primary key made up of both fields.

3rd party EDIT: [to clarify, since people may not see all of the comments] many of the comments below were based on the initial answer which only included the first line and was interpreted as meaning a separate table per user.

Upvotes: 1

xkeshav
xkeshav

Reputation: 54016

As per Database Normalization perspective; it';s better to have multiple tables with foreign key contraints...

reference Links:

http://databases.about.com/od/specificproducts/a/normalization.htm

http://www.devshed.com/c/a/MySQL/An-Introduction-to-Database-Normalization/

Upvotes: -1

Gerrat
Gerrat

Reputation: 29690

You should make one big table of file records, and simply include a column called "user". You do NOT create separate tables that store the exact same information.

EDIT: You should create one big table with a column that stores a reference to the user. It has been suggested that userid would be an appropriate name, but you can call it whatever you like. The actual user information would need to be in a separate table (linked by this key).

Upvotes: 5

Related Questions