MySql DB Design question

Question

Assuming I have an application where I expect no more than 50,000 users.
I have 7 tables in my DB schema. Is it a good idea to replicate all the
tables for every user? So, in my case, number of tables will roughly be
50,000 * 7 = 350,000. Is it in anyway better than 7 monolithic tables?

Answer 1

NO, I would not recomend creating a table schema per user in the same database.

mysql should handle the load perfectly well, given the correct indexes on tables.

Answer 2

What you're proposing is horizontal partitoning. http://en.wikipedia.org/wiki/Partition_%28database%29 i.e. you take all the rows in what would (logically) be one table, and you spread those rows across multiple tables, according to some partitioning scheme (in this case, one table per user).

In general, for starter applications of this nature, only consider partitioning (along with a host of other options) when performance starts to suffer.

Answer 3

Certainly not. A DB is typically optimized for having multiple rows in a given table... Try to rethink your DB schema to have a field in each of your tables that holds the user's ID or another associative table that holds the user id and key for the particular entry in the data table.

There's a decent intro to DB design here.

Answer 4

No, it's definitely not. For two reasons:

1. The database design should not change just because you store more data in it.

2. Accessing different tables is more expensive and more complicated than accessing specific data in a single table.

Just image if you wanted statistics for a value from each user. You would have to gather data from 50 000 tables using 50 000 separate queries instead of running a single query against one table.

A table can easily contain millons of records, but i'm not sure that the database can even contain 350 000 tables.