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

  • Thread starter Thread starter Todd
  • Start date Start date
T

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!
 
Normalize, Normalize, Normalize,.. ;-)
Whether you store Null values depends on your business rules.
 
Do I keep the 6 columns in the main table?
Click to expand...

No way!
Or, do I create a child table with each row holding the grade for each aspect?
Click to expand...

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]
 
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
 
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.
 
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
 
Back
Top