optimal database design

What is more efficient for a database design - a lot of tables with only a
few records or a few tables with lots of records.
I'm starting a new site and each user will have numerous records but I'm not
sure whether to have a few very large tables (over 100,000 rows) or start a
new table for each user which would result in approx 1500 tables most of
which would be the same table design with different rows.
I'm using SQL2000.
I guess this is quite a basic question, but I'm a bit unsure.
Any references anyone could point me too as well.
ThxDatabase design is based on the analysis of data entities of really world,
rarely on the amount/quantity of an entity. Usually an entity is translated
into a table in database, say, you have a database with Customers table,
Orders table, Order Details table... You do not create many customer tables
just because of too many customers' records, it is not optimal, it is simply
wrong. BTW, a table with a million rows may not be as that big as you
thought, unless the table has a lot columns and the columns' size is big.
For SQL Server, there is not problem to handle a database with a few
million-row tables with regular columns (say, a couple of dozen columns,
mostly numbers and short texts)
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
not
> sure whether to have a few very large tables (over 100,000 rows) or start
a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>|||100K rows is not a lot by any means in Sql Server. Trying to manage a
different table for each use sounds like a nightmare that you should wake up
from as soon as possible.
--
Andrew J. Kelly SQL MVP
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
> not
> sure whether to have a few very large tables (over 100,000 rows) or start
> a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>|||A new table for each user doesn't sound like a very sensible design.
Each table should represent a single entity - that normally means one
table for all things that have a common set of attributes (columns).
100,000 rows is very small in SQL Server terms (to most people anyway)
but whatever the size, start with a normalized logical design - then
think about performance considerations if performance evaluations
demonstrate you may have problems.
--
David Portas
SQL Server MVP
--