An array within a table--Break into 1-to-1 mini tables or separate rows?

[Todd]

I have a table that needs to hold the monthly grade of our members
locations. There are 6 aspects to this grade. Now, each inspection will
always give a grade for each of the 6 aspects. Do I keep the 6 columns in
the main table? Or, do I create a child table with each row holding the
grade for each aspect?

AND, if the grade is zero, do I not add the row for that aspect? IE: when I
get a null row while looking for the row for that aspect, do I count the
value as zero?

Should I just keep all 6 aspects within the main table?

Also, there are 4 other groups of grades like this. So, in reality there
are 40 columns like this!!

The un-normalized table:
ID PK
Name Varchar
Age Int
Grade_CommunicationRating Int
Grade_Cleanliness Int
Grade_TaxCompliance Int
Grade_Staffing Int
Grade_Insurance Int
Grade_Stocking Int
Grade_OVERALL Int


Normalized:

MAIN TABLE:
ID PK
Name Varchar
Age Int

GRADES TABLE:
ID PK/FK
GradeType PK
Rating Int


Thanks for your help!

 

[Duane Hookom]

Normalize, Normalize, Normalize,.. ;-)
Whether you store Null values depends on your business rules.

 

[John Vinson]

Do I keep the 6 columns in the main table?

No way!

Or, do I create a child table with each row holding the grade for each aspect?

Absolutely.

"Fields are expensive. Records are cheap."

If you have six aspects today... you very well may have eight next
Spring, or four. With the normalized structure, no problem; with one
field per aspect, you must redesign your tables, your queries, your
forms, and your reports when you add/remove an aspect.

John W. Vinson[MVP]

 

[Todd]

Guys, thanks for your quick answers!

The reason I kept leaning towards putting the six grade fields into 1 table
instead of making them into 6 records is that, when a user enters the
grades, all six records must be created anyway.

6 child records created for every parent record. Is this a bad thing?
(This kind of made me want to just lump them all into the parent table.)

Thanks alot!


---------------
The un-normalized table:
ID PK
Name Varchar
Age Int
Grade_CommunicationRating Int
Grade_Cleanliness Int
Grade_TaxCompliance Int
Grade_Staffing Int
Grade_Insurance Int
Grade_Stocking Int
Grade_OVERALL Int


Normalized:

MAIN TABLE:
ID PK
Name Varchar
Age Int

GRADES TABLE: (Six records created everytime a new MAIN TABLE rec created)
ID PK/FK
GradeType PK
Rating Int

 

[Duane Hookom]

As John stated "Fields are expensive. Records are cheap."

User interface should not drive table structures. If you have questions
about creating a user interface for easy data entry, ask away.

 

[Todd]

Whoa. I think you caught me. This is why I keep on choking on creating
multiple rows for keeping the grade values. I think I just keep wondering:
how am I gonna populate the form without doing a ton of vba coding .

Putting all the rows into a single table lets me do a quick form but kills
normalization.

Any hints on how to easily display all six Yes/No records on 1 form as
checkboxes?

Thanks

 

[Duane Hookom]

If you don't mind a vertical view, append the six records with the
appropriate foreign keys and display them in a subform for editing. The "At
Your Survey" sample uses this functionality in appending survey question
records http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.