Tables - to split or not to split

[Guest]

Hi,

My database is going to contain information of around 70 counties. The
county data will consist of 4 or 5 fields and I will use other tables and
queries to interact with the county data.

My key question is this: should I keep all of the county data in one huge
table (could be as much as 400,000 records!) or should I split each county up
into its own individual table.

I can see pros and cons for both - in my mind, running queries off one table
is easier than messing around with 70 different tables...but then again, one
table is likely to me much more unstable than 70 individual ones (or is it?).

What is best in terms of good database design?

Kevin

 

[Douglas J. Steele]

Tables are intended to represent Entities.

If your table represents a single entity (so that Country is simply an
attribute of the record), then all your records should be in a single table.

If there are separate attributes associated with the table depending on the
country, then you might consider multiple tables.

In 99.9% of cases, one table is the appropriate solution.

 

[John W. Vinson/MVP]

Hi,

My database is going to contain information of around 70 counties. The
county data will consist of 4 or 5 fields and I will use other tables and
queries to interact with the county data.

My key question is this: should I keep all of the county data in one huge
table (could be as much as 400,000 records!) or should I split each county
up
into its own individual table.

I can see pros and cons for both - in my mind, running queries off one
table
is easier than messing around with 70 different tables...but then again,
one
table is likely to me much more unstable than 70 individual ones (or is
it?).

What is best in terms of good database design?

One table, with an indexed County field.

400,000 records is respectable but not big. 4,000,000 is getting big.
40,000,000 is probably too big for Access, though I know of databases with
such tables.