Linking tables

[sven2]

Hello,

I want to register some information (evaluations) about new employees in the company. After they have started (startdate) every employee has to get an evaluation after 1 and 3 months.

To store these data I have made 4 tables like:

employee, evaluationdate, evaluationscore, evaluationparts ...

what is the best way to link these tables (see example) ?

Thanks in advance,
Sven.

 

[stopher]

Is my understanding of your model right?....

An employee has an evaluation consisting of several parts (3 in your example). An employee is only ever evaluated on each part one. These parts can be evaluated on different days. The evaluation of each part for each employee is given a score.

This would lead me to a structure of only three tables:

Employee
Personnumber (PK)
StartDate

EvaluationDetail
Personnumber (PK) (related to Personnumber in table Employee)
EvaluationID (PK) (related to EvaluationPartID in table EvaluationPart)
EvaluationDate
EvaluationScore

EvaluationPart
EvaluationPartID (PK)
PartName

The only bit that doesn't fit this is Actief (whatever that is).

I've used the name EvaluationDetail because a single record in this table doesn't represent an Evaluation as a whole. In the model I've given you can store the overall assessment (assuming you can't calculate it) in the Employee table e.g. EvaluatioResult: pass/fail. This assumes that an emplyee only ever has one evaluation. What happens if they fail their first evaluation?

hth
Chris