A lot of records in database [closed]

Closed. This question is off-topic. It is not currently accepting answers.

---

Want to improve this question? Update the question so it's on-topic for Stack Overflow.

Closed 9 years ago.

开发者_StackOverflow社区 Improve this question

I'm planning a project site where I will have about 10 thousand users. In service will be about 5 thousand static products stored in one table. Each user will have minimum 1 thousand products (max 5 thousand). And now I have a question: how to design a relational model? Of course users tables is OK.

I will keep about: 10,000(users)*1,000(products) = 10,000,000 records. I have one idea: create (for example) 100 tables and assign each products of user to only one table. The user will update about 50 records every day.

I would like to use symfony 1.4, doctrine 1.x and MySQL/PostgreSQL. It's good idea?

---

100's of tables with the same definition? Sorry but it is a terrible idea. Databases nowadays are plenty capable of handling multi million row tables.

---

I don't know about the frameworks, but MySQL or PostgreSQL should be able to handle it without an issue. However, I would advise against having a separate table for each user, unless you're giving users direct database access (ie: not through a webpage where you've configured only specific output to be shown) and assigning them their own connection credentials so they can't view other users' product information.

I think a much better idea would, for MySQL, at least, be to just have a few InnoDB tables and restrict the results of their searches/edits to records with a userid matching their currently logged in ID. Having a bunch of extra tables is going to waste a lot of space (IIRC, every InnoDB table has a minimum size of 10 MB of required space usage) and your application would, eventually, be restricted by the 64TB maximum solely from the amount of individual user tables you would have created.