Multiple tables or one mega-table in SQL?

Question

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.

Answer 1

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).