How to design a large DB?

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,
KevinThe right design depends on:
What's the total size (GB) of data?
What's the operations you want to do with the data and how fast you want
them to be?
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin" <Kevin@.NoSpam.home.nl> wrote in message
news:%23Wtc6i2JFHA.3928@.TK2MSFTNGP09.phx.gbl...
> 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
>|||Kevin
Don't create 1 database for each use, moreover you said that table's
structure is the same. It's hard to maintain, optimize, tune. SQL Server
will need to use more system resource to maintain the databases.
"Kevin" <Kevin@.NoSpam.home.nl> wrote in message
news:%23Wtc6i2JFHA.3928@.TK2MSFTNGP09.phx.gbl...
> 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
>|||"Kevin" <Kevin@.NoSpam.home.nl> wrote in message
news:%23Wtc6i2JFHA.3928@.TK2MSFTNGP09.phx.gbl...
> 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?
>
This one is easy. Option 1.
David|||I'm with all the others. Assume a single set of tables and a single
database for all users. Presumably the User ID will be part of the key
in these tables.
Throughout you've described your DB as "large" without telling us the
actual size in bytes. SQL Server can handle many terabytes of data. The
best strategy is to design your logical model first (logically you put
like data in a single table, not partitioned across multiple tables)
then estimate sizes and tune performance when you are ready to create
physical databases and test-data.
--
David Portas
SQL Server MVP
--|||Thanks All,
After your opinions, I'll definitely use option 1. The data is not really
huge, but our server machine is not very good and a little difficult to
manage each user's data.
Best regards,
Kevin
"Kevin" <Kevin@.NoSpam.home.nl> wrote in message
news:%23Wtc6i2JFHA.3928@.TK2MSFTNGP09.phx.gbl...
> 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
>|||"Kevin" <Kevin@.NoSpam.home.nl> schrieb im Newsbeitrag
news:OCQefgEKFHA.2716@.TK2MSFTNGP15.phx.gbl...
> Thanks All,
> After your opinions, I'll definitely use option 1. The data is not
really
> huge, but our server machine is not very good and a little difficult to
> manage each user's data.
Then definitely use option 1. Note, that you might not need to put the
user id into all tables. For example, if one of the main table is a
contact table and each contact has n adresses (sitting in another table)
then you'll have a contact id which is used for joining the adresses of
this contact. Then only the contacts table needs a user id. Or you have
an additional table that has user id, contact id and no user id is needed
in the contact table. But that really depends on the nature of your data
and what you want to do with it.
Kind regards
robert