Hi All,

I'm trying to design a large DB and considering which direction to go.
The planned login user accounts will be 10,000, and each user has 15 DB
Tables. Each Table's structure similar to other users, but user's data
records have no relation with each other.

1. Create 1 Database for all users, and an User_Login Table containing
User_ID, and 15 other Tables each containing the related User_ID.
This has 15 + 1 Tables in 1 Database, but each Table could be huge (10,000 X
each user's data record).

2. Create 1 Database for each user.
This is easier to manage each user, but there'll be 10,000 Databases.

3. Create 1 Database for all users, and 15 Tables for each user.
This could result a DB of 15 X 10,000 Tables.

I also have to consider how to manage each user's data, i.e, backup, limit
size, delete outdated records ...
Please enlighten me how to design such a DB. Is there an optimalized
structure for both programming and management?


Best regards,
Kevin

What's appends during upgrades or changes in the schema? Does your 10 000
clients must be updated at the same time?

What about the volume? does each client use 1mb or 1gb of data?

Maybe you can mix the solution...
Some tables can be shared while other tables are unique by user (specially
the fact table)

Big shared tables impact the query performance. allways filtering with the
UserId will degrade the performance on the biggest tables.

Unfortunatly you can't use partioned views, because there is a limitation of
256 tables in 1 view.

"Kevin" <Kevin (AT) NoSpam (DOT) home.nl> wrote