One big table versus several small tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jsccorps - 14 May 2005 16:37 GMT

My database is in the initial phase and I would like some design pointers
before getting too far down the road.

My DB currently is 70M. I have a main table with 20 fields and about 120K
records. It grows about 10K records per month. Now, I have some new fields
that I need to add. Performance wise, is it better to keep adding fields to
the main table or create another table with a relationship with the main
table. I'm concerned that if I add a new table that's linked (via
relationship) to the main table that my queries performance will be slowed.
Is there a rule-of-thumb for table length versus mutlple linked (via
relationship) tables?

Reply to this Message

Douglas J. Steele - 14 May 2005 18:30 GMT

The more important question is whether those fields belong in the one table,
or if they should be in a separate table.

While Denormalized tables may be faster for data retrieval, they almost
always lead to considerably more work when updating the data.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> My database is in the initial phase and I would like some design pointers
> before getting too far down the road.
[quoted text clipped - 10 lines]
> Is there a rule-of-thumb for table length versus mutlple linked (via
> relationship) tables?

Reply to this Message