Correct model for a database with a table for each user.

Question

Kinda stuck here... I have an application with lets say 5000 rows of data per user and was wondering if it was right or wrong to do it this way:

On user account creation a new table is created (UserData_[UserID])

or should I just have 1 table for userdata and have everything in there with a column for userid?

The reason I am stuck at the moment is that it seems NHibernate isn't able to be mapped to dynamic table names without creating another ISessionFactory which has alot of overhead AFAIK.

Any help would be greatly appreciated.

Thanks.

Answer 1

One entity type, one table.

So, use one table for all users with a userid column unless you have unusual requirements elsewhere in the application.

Answer 2

Dear god NO, this is the wrong way. Don't create a table for each user.

To know what exactly to do, we need to know what's in the 5000 records.

If, for example, each user has 5000 Widgets, put the Username and UserID in one table and all the Widgets (each with a UserID) in another.

Answer 3

One table is enough. 5000 rows per user, 100.000 users would ONLY be 500.000.000 rows - and that is no ta lot by SQL standards ;)

In general, db schema should be as static as possible.

Answer 4

Don't use different tables for one entity type. Use one table for all users and then you won't even have the problem of dynamicly created tables. Try to imagine how hard it will be to query your data, and how unlogical it is to query 5000 tables to get all users.