Lots of Fields or lots of records

[DawnTreader]

Hello All

i have a table that as it is currently designed has a huge amount of fields,
and as time goes on i am sure that the number of fields might change to
include more.

basically this table is going to hold evaluation information. for instance
an employee will be scored on 91 of the 96 fields in this table. there are
some groupings of the type of score, for instance one set of fields deals
with electrical knowledge. the 6 electrical knowledge fields are things that
the employee needs to understand and improve on as he continues to work for
the company.

evaluations will be done on a regular basis so there are a few date fields
in this table as well as a primary key and employeeID. i may do an evaluation
today and one in 6 months for the same employee.

each field will store a number between 0 and 5 so it is a small amount of
numerical data per field, but i am curious if a big honking flat table is the
best way to go with this or if i need to normalise this data?

i was thinking about spliting the table into each category and making a
table that stores the list of categories for the evaluation. but i am very
unsure about the best structure.

do i have a table with fields like this:
TechRatingID
EmployeeID
CreateDate
AssessmentDate
CompletedDate
Electric1
Electric2
Electric3
Electric4
Electric5
Electric6
Electric7
Electric8
Electronic1
Electronic2
Electronic3
Electronic4
Electronic5
Electronic6
Mechanical1
Mechanical2
Mechanical3
Mechanical4
Mechanical5
Mechanical6
Mechanical7
Mechanical8
ProductK1
ProductK2
ProductK3
ProductK4
ProductK5
ProductK6
Dispenser1
Dispenser2
Dispenser3
Dispenser4
Dispenser5
Dispenser6
Commissioning1
Commissioning2
Commissioning3
Commissioning4
Commissioning5
Commissioning6
Installation1
Installation2
Installation3
Installation4
Installation5
Name
Installation6
Safety1
Safety2
Safety3
StationObs1
StationObs2
StationObs3
Troubleshooting1
Troubleshooting2
Troubleshooting3
Troubleshooting4
Troubleshooting5
Troubleshooting6
TopRebuild1
TopRebuild2
TopRebuild3
TopRebuild4
TopRebuild5
TopRebuild6
BtmRebuild1
BtmRebuild2
BtmRebuild3
BtmRebuild4
BtmRebuild5
Tubing1
Tubing2
Tubing3
English1
English2
Computer1
Computer2
Computer3
Workmanship1
Workmanship2
Workmanship3
Workmanship4
Communication1
Communication2
Presentation1
Presentation2
Attitude1
Attitude2
Dependability1
Dependability2
Discrecionary1
Discrecionary2

there is already an employee table, but i am unsure how to break down the
data layout above.

 

[PieterLinden via AccessMonster.com]

Hello All

i have a table that as it is currently designed has a huge amount of fields,
and as time goes on i am sure that the number of fields might change to
include more.

Dead giveaway. This structure is absolutely wrong. I once had to deal with
tons of databases set up like this and it wasn't pretty. You would probably
be better off with something like

Person---(1,M)---Evaluation----(1,M)-----SkillRating----(M,1)----Skill---(M,1)
----SkillGroup


SkillRating(EvaluationID, SkillID, Rating(0-5))

Most everything in your list is a *record* in the "Skill" table (like
Electronics 1, Electronics 2, etc)

You can pretty much tell your structure is wrong if...
1. you need to modify your table structure continually as you store new facts.

2. querying the database is a *nightmare* - for example, show me all of
Person X's skills that are above 3. What SkillGroup are most of them in?
You can't do it if you store the Skills in field *names*. Facts belong *in*
the table (in a record), not *on* it (in a column name).