Table structure question

G

Guest

Have another question. I've split most of my tables so that 90% of them
contain only 2-4 fields and then related them (tblCompetencies,
tblWardCompetencies, tblPersonnel, tblPersonnelCompetencies, etc).

Question: How many fields is too many to still remain normalized?

I have three tables left to split and I don't think I should.

1. tblArmyPhysicalFitnessTest
APFT_ID (PK)
PersonnelID (fk ---> tblPersonnel.PersonnelID (PK)
APFTInfo (18 fields----> Type (Record/Diagnostic), Date, Result (pass/fail),
PushUpRawScore, SitUpRawScore, RunRawScore, PushUpTableScore,
SitUptableScore, RunTableScore, MeetsWeight (Y/N), AllowedWeight, Profile
(Y/N), Profile Type, Height, Weight, SpecialPOPSReason, SpecialPOPSDate.

Each record would contain one complete APFT. Some fields are Date/Time, some
are Text, Yes/No, and some are Number.

The other two are similar in that they (tblMaleWeightControl and
tblFemaleWeightControl) contain only the fields needed to conduct one bodyfat
measurement. Male has 13 fields + 1 PK + 1 FK and Female has 26 fields + 1 PK
+ 1 FK.

Split and relate or not to split/relate?
 
J

Jeff Boyce

Normalizing your data structure is NOT about minimizing the number of
fields. There is no "right" number of fields.

Normalizing is about removing redundancy, and eliminating multi-use fields,
and making sure that like data is all in one table.

The general consensus in this newsgroup is that well-normalized tables with
as many as 30 fields are a rarity. That isn't because having more than 30
fields is wrong, it's because it is rare that it takes that many fields to
hold the attributes directly related to an entity.

The tables you listed cause me concern just by their names. For example,
having one table with "Male" in the title and another with "Female" in the
title tells me that you've embedded data in the table names. Would it be
possible to use a single table with the fields you've split into these two,
plus one more field that holds "M" or "F"?

Your [tblArmyPhysicalFitnessTest] appears to contain multiple
items/scores/ratings/responses as individual fields. Here's the problem
with that design ... if you EVER need to change the number of
sub-tests/items that comprise the ArmyPhysicalFitnessTest, you'll have to
change this table, then change any queries that rely on it, then change any
reports that rely on either the queries or the table directly, then change
any forms that rely on the queries or the table directly, then change any
code that references the table or the related queries. Do you really want
to have to do all that work every time the ArmyPhysicalFitnessTest gets
changed?

If you created a table that held the items tested (i.e., all items used in
the test), you could modify the items in the test by adding a new row.

If you create a table that holds "response/result" data, you could get by
with something like:

tblAPFTResponse
ResponseID
TestedPersonID
ItemTestedID
ResultType (you mentioned Date/Time, Y/N, text, ...)
Result

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
G

Guest

Thanks for the advice. Seeing how the APFT hasn't changed the way it is
conducted for at least the last 15 years, I think I'll be safe with keeping
it the way it is :)

I'll definetly keep the tbls that convert rawscore to true score (based on
age) in their own three tbls! That HAS changed!

Will go ahead and redo the bodyfat tbls....it makes sense NOW

That's why you're the MVP!
Thanks!
Dan


Jeff Boyce said:
Normalizing your data structure is NOT about minimizing the number of
fields. There is no "right" number of fields.

Normalizing is about removing redundancy, and eliminating multi-use fields,
and making sure that like data is all in one table.

The general consensus in this newsgroup is that well-normalized tables with
as many as 30 fields are a rarity. That isn't because having more than 30
fields is wrong, it's because it is rare that it takes that many fields to
hold the attributes directly related to an entity.

The tables you listed cause me concern just by their names. For example,
having one table with "Male" in the title and another with "Female" in the
title tells me that you've embedded data in the table names. Would it be
possible to use a single table with the fields you've split into these two,
plus one more field that holds "M" or "F"?

Your [tblArmyPhysicalFitnessTest] appears to contain multiple
items/scores/ratings/responses as individual fields. Here's the problem
with that design ... if you EVER need to change the number of
sub-tests/items that comprise the ArmyPhysicalFitnessTest, you'll have to
change this table, then change any queries that rely on it, then change any
reports that rely on either the queries or the table directly, then change
any forms that rely on the queries or the table directly, then change any
code that references the table or the related queries. Do you really want
to have to do all that work every time the ArmyPhysicalFitnessTest gets
changed?

If you created a table that held the items tested (i.e., all items used in
the test), you could modify the items in the test by adding a new row.

If you create a table that holds "response/result" data, you could get by
with something like:

tblAPFTResponse
ResponseID
TestedPersonID
ItemTestedID
ResultType (you mentioned Date/Time, Y/N, text, ...)
Result

Regards

Jeff Boyce
Microsoft Office/Access MVP

SFC Traver said:
Have another question. I've split most of my tables so that 90% of them
contain only 2-4 fields and then related them (tblCompetencies,
tblWardCompetencies, tblPersonnel, tblPersonnelCompetencies, etc).

Question: How many fields is too many to still remain normalized?

I have three tables left to split and I don't think I should.

1. tblArmyPhysicalFitnessTest
APFT_ID (PK)
PersonnelID (fk ---> tblPersonnel.PersonnelID (PK)
APFTInfo (18 fields----> Type (Record/Diagnostic), Date, Result
(pass/fail),
PushUpRawScore, SitUpRawScore, RunRawScore, PushUpTableScore,
SitUptableScore, RunTableScore, MeetsWeight (Y/N), AllowedWeight, Profile
(Y/N), Profile Type, Height, Weight, SpecialPOPSReason, SpecialPOPSDate.

Each record would contain one complete APFT. Some fields are Date/Time,
some
are Text, Yes/No, and some are Number.

The other two are similar in that they (tblMaleWeightControl and
tblFemaleWeightControl) contain only the fields needed to conduct one
bodyfat
measurement. Male has 13 fields + 1 PK + 1 FK and Female has 26 fields + 1
PK
+ 1 FK.

Split and relate or not to split/relate?
Click to expand...
Click to expand...
 
G

Guest

Thanks for the advice. Seeing how the APFT hasn't changed the way it is
conducted for at least the last 15 years, I think I'll be safe with keeping
it the way it is :)
Click to expand...

It isn't just about whether the data will change (and 15 years just means it
is due for a change), it is also about having well-normalized data that you
can pull information from easily. For instance, with 30 fields, you will
need to create queries/forms/reports that specifically name those 30 fields.
With those fields being normalized, your queries/forms/reports will only need
to specify about two fields. What may appear to be the easy way to do it can
actually end up costing you a lot of time and grief.

If you have the time, I would challenge you to try an experiment. Keep your
current table, but also set up several normalized tables that will contain
the same information. Then create a query, form and report for both types of
tables, using the default fonts. Now go in and change the fonts to be larger
on all of them. Keep track of how long it takes to do that. Make sure that
everything is still readable after changing the font size, including the
labels and the data.

Now, using the same queries, apply a filter in them so that only height,
weight, and RunTableScore show up. Now look at the forms that depend on
those queries. What is the difference between them?

Normalization is a way of making the data easier and faster to manipulate
and view. Whenever you try to make a database into a spreadsheet, you are
creating problems for yourself.
 
Top