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,
KevinKevin wrote:
> 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?
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I'm not sure what you're trying to accomplish, but, off the top of my
head, option 1 seems like the way to go.
You say each user's tables have "similiar" structures. Do you mean they
have the same columns w/ the same data types, but, because the data
"belongs" to one user that table is different from other users' tables?
Do you know that you can add a column that identifies which user that
row belongs to, and set up Views that will filter the data according to
the user login/role?
Perhaps if you posted the DDL of the main tables in your DB, this
newsgroup could give better advise.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjOa84echKqOuFEgEQJjiACfezgspI93YOfc
AebKnzS7A2NzT2IAn0cQ
JuPOTzeb/CpSNQTgTOPBqa/j
=tzPC
--END PGP SIGNATURE--|||Hi MGFoster,
Thanks for your reply!
The option 1 is exactly as you describe:
"they have the same columns w/ the same data types, but, because the data
"belongs" to one user that table is different from other users' tables? Do
you know that you can add a column that identifies which user that row
belongs to,"
And I would the column "User_ID" to identify each user's data record, but
this could result an huge DB Table and might be difficult to manage each
user's data, size and so on.
Best regards,
Kevin
"MGFoster" <me@.privacy.com> wrote in message
news:%VMYd.8821$cN6.3955@.newsread1.news.pas.earthlink.net...
> Kevin wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> I'm not sure what you're trying to accomplish, but, off the top of my
> head, option 1 seems like the way to go.
> You say each user's tables have "similiar" structures. Do you mean they
> have the same columns w/ the same data types, but, because the data
> "belongs" to one user that table is different from other users' tables?
> Do you know that you can add a column that identifies which user that
> row belongs to, and set up Views that will filter the data according to
> the user login/role?
> Perhaps if you posted the DDL of the main tables in your DB, this
> newsgroup could give better advise.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQjOa84echKqOuFEgEQJjiACfezgspI93YOfc
AebKnzS7A2NzT2IAn0cQ
> JuPOTzeb/CpSNQTgTOPBqa/j
> =tzPC
> --END PGP SIGNATURE--|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
How big is your definition of "huge"? I don't have the figures at hand,
but I believe a SQL'r DB can be up to 4TB (that's terabytes or 4 trillon
[2^40] bytes). You can compute the possible size of a DB w/ this
formula, per table:
sum of columns' data type sizes * number of rows
Example:
A table has 4 columns:
integer 4 bytes
datetime 8 bytes
varchar(20) 20 bytes
char(2) 2 bytes
--
Total Bytes 34 bytes per row * 10,000 rows = 340,000 bytes (approx).
This is tiny for today's drive capacities.
Then you'd have to include any indexes.
You can limit size of tables by judicious attention to table design.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjOqCYechKqOuFEgEQI5cACg79P2eYBhRJzf
tXVQG3XEa1aUS4YAn2mm
bJzsRvjC53YhA74OiSV5l1Wy
=ktnN
--END PGP SIGNATURE--
Kevin wrote:
> Hi MGFoster,
> Thanks for your reply!
> The option 1 is exactly as you describe:
> "they have the same columns w/ the same data types, but, because the data
> "belongs" to one user that table is different from other users' tables? Do
> you know that you can add a column that identifies which user that row
> belongs to,"
> And I would the column "User_ID" to identify each user's data record, but
> this could result an huge DB Table and might be difficult to manage each
> user's data, size and so on.|||The 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
>|||Using option 1 would still be considerably less overhead than creating
separate databases or even separate tables for each user :) Not to mention
your administration will be easier, and your report generation will be
simpler (if you wanted to compare user statistics, for instance, or
calculate averages for all users). It would probably be easier to join a
few tables in a single database than it would be to join 10,000 separate
tables in a database; or 10,000 separate tables in separate databases just
to get totals on two fields (example).
"Kevin" <Kevin@.NoSpam.home.nl> wrote in message
news:OLZcPK3JFHA.3196@.TK2MSFTNGP15.phx.gbl...
> Hi MGFoster,
> Thanks for your reply!
> The option 1 is exactly as you describe:
> "they have the same columns w/ the same data types, but, because the data
> "belongs" to one user that table is different from other users' tables? Do
> you know that you can add a column that identifies which user that row
> belongs to,"
> And I would the column "User_ID" to identify each user's data record, but
> this could result an huge DB Table and might be difficult to manage each
> user's data, size and so on.
> Best regards,
> Kevin
>
> "MGFoster" <me@.privacy.com> wrote in message
> news:%VMYd.8821$cN6.3955@.newsread1.news.pas.earthlink.net...
>|||"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
>