When to use more than one data table

[KerryL]

How is one to know when it's wise to store data in more than one table?

For example, I'm creating a new db that will store approximately 20k records, each record contaning about 50 fields. None of the fields are large except for the 3 comment fields.

Can I get by with one data table (with the possible exception of the comment fields) or should I break the data down into a few separate tables?

Just curious as to how it should be determined.

Thanks in advance,
KerryL

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[traingamer]

If you have one and only one occurance of each field, then, by all means, use only one table.
If, however, you have fields like dependent#1, dependent#2, etc., those fields would belong in a 'dependent' table.

You should rarely split a table just to have smaller tables.
It sounds like moving the 'large comment' fields to another table would be a good idea.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[KerryL]

Thanks guys.

traingamer, I do have a couple instances of repeating fields:

Layoff Reason 1
Layoff Reason 2
Layoff Reason 3
and
Serv Del Vec 1
Serv Del Vec 2
Serv Del Vec 3
Serv Del Vec 4

However, each instance of the "Layoff Reason" and the "Serv Del Vec" will be pulled from a corresponding table of options. IOW, there could be one Layoff Reason or there could be 3, all chosen from the same list of 10 options in tblLayoffReasons. If that warrants a separate table can you help me understand why?

Thank you.

 

[Golom]

The real issue (see the paper that PHV references) is ... is every non-key field in the table dependent on
- The Key,
- The Whole Key and
- Nothing But the Key?

If they are then you have a normalized table (at least to 3^rd normal form) and the fields should be legitimately in the same table. If they are not then you have internal dependencies and multiple tables will be needed to achieve a normalized database.

In my experience, very few entities have as many as 50 independent attributes (i.e. non-key fields) so I'm suspecting that you are a multi-table candidate.

 

[traingamer]

If that warrants a separate table can you help me understand why?

Briefly, you don't want to re-design the database when someone (the boss, regulatory requirements, that one non-standard employee) needs a 4th layoff field or a 5th 'Serv Del Vec' whatever that is. Your Layoff table may be something like:
Key EmployeeID LayoffReason Date ...

Those employees never laid off require no space in the database. Those laid off 50 times require no changes to database structure.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[KerryL]

The more I look at my data, the more I think you're right. There seem to be 4 categories of data:

1. Company information
2. Workforce Demographics
3. Incident & Notification Info
4. Response & Service Delivery Info

I guess the thing that makes me wonder if I need to break it up is the fact that when data is entered for a company, it will only be entered once.

A company will not have multiple entries for items 2-4. This data will be entered once for each company, so I figured one record in one table for each company to help keep the design simple.