Tables - many smaller vs. 1 large

[MaBell]

Hi Guys,

What do you think is better functionality and design wise:

One large table (avg import 135,000 records a fiscal quarter) OR 15 smaller
tables (avg 9,000 records a fiscal quarter) with 15 times as many queries and
reports?


I have a FE/BE with only a few users.


Thanks in advance,
Matt
(access 2000)

 

[Rick B]

Personally, I keep all my records in one table (or several tables using
relationships where like data is maintained). I never break records into
separate tables based on age. You just never know when you might want a
report that pulls all records for a particular range that may span several
different quarters.

I break large records into smaller related records (name and address in one
table, personal data in another, etc.) but never break the data into smaller
identical tables (ContactsA-N in one table, Contact O-Z in another). Just
not good design.

Just my opinion,

Rick B

 

[Thomas Tomiczek [MVP]]

Larger? 135000 etnries, even in ms access, is NOTHING.

One table.

--
Regards

Thomas Tomiczek
THONA Software & Consulting Ltd.
(Microsoft MVP C#/.NET)
(CTO PowerNodes Ltd.)