每个用户有多个SQL表? [重复]

This question already has an answer here:

I have a small file hosting website that I am trying to store the files that users upload in a database. The issue that I am having is that I cannot decide which method would be better:

  1. To store all users in one table
  2. create a new table for each user.

I understand that the second method will slow performance but by how much? I am planning on having 1000+ users eventually. The issue with the first method is listing the files back to the user. What method should I go with and which one would be the most efficient?

</div>

Short answer:

No. Use the simplest thing that works: A single table.

Long answer:

You'll know what kind of scaling problems when you have a production system under production loads, and then you can analyze where your bottlenecks are and develop a sharding strategy based on real-world use cases and not hypotheticals.

Right now you're just guessing, and you'll probably guess wrong. Then you're stuck with an awful database structure you'll find impossible to undo.

Try not to store actual files in the MySQL database, this almost always leads to horrible disaster, but instead store them on the filesystem and keep references to them in the database. If you're going to be managing a lot of files, heaps and tons of them, you may want to look at document store database like Riak to help with that.

I suggest creating a table for each entity and having a correct relationship between them.

For example: Users table will have user_id, user_name, etc. Files table will have id, url, user_id

In this case, the relationship is created by having the same user_id. So when you upload a file, you attach the user_id to the image.

That means - no, don't create a separate table for each user. Go with method 1 and make sure each important entitiy has its own table.

Down the road, you will probably have more entities and more tables such as Permission, Products, etc etc. By using SQL queries you will be able to get all the data you want.

Hope this helps!

Having 1000 ish users is not a problem for MySQL. But tadman is rigth, save the files on the filesystems instead of the database.

If you know that you will endup with millions of users, I suggested that you read on how Facebook or others big users related sites handle this scalling problems.