Relationships Issue - multiple fields, many to many

[anugroho]

Hi All,

I'm constructing a db now which going to record trainings, trainers and evaluation. The training should have information of name of the trainer and it's possible to have more than one trainer. At the end of the training, all trainers are assessed individually and given a score (scale 1-4). Each trainer can be involve also in more than one training.

Now, in the database, I want to capture all the scores of a trainer for all the trainings s/he facilitated. These scores will be averaged to conclude whether s/he is a good trainer.

I see this as many-to-many relationship, which is already complicated, and additionally there is problem of capturing the score. In my db construct, I have now 2 tables: training and trainers.

Training table:
- training_ID - primarykey
- training_title - text
- training_trainer1 - number - linked to trainers tbl (lookup)
- training_trainer2 - number - linked to trainers tbl
- training_trainer3 - number - linked to trainers tbl
- training_trainer_eval1 - number - eval score for trainer1
- training_trainer_eval2 - number - eval score for trainer2
- training_trainer_eval3 - number - eval score for trainer3

My question for the above: is it correct to make multiple fields like that? Do you have better suggestion? How should the relationship be to the trainerstbl?

Trainers table
- trainer_ID - primarykey
- trainer_name - text
- trainer_eval - calculated field taking from average of eval score

My question for the above: is it possible to do like that (averaging and put into a field?)? How would you suggest ?

 

[jdraw]

Who is being trained?
Is this a typical
student -- course --- instructor scenario?

This type of structure

- training_trainer1 - number - linked to trainers tbl (lookup)
- training_trainer2 - number - linked to trainers tbl
- training_trainer3 - number - linked to trainers tbl
- training_trainer_eval1 - number - eval score for trainer1
- training_trainer_eval2 - number - eval score for trainer2
- training_trainer_eval3

indicates your tables are not Normalized. Your tables should be Normalized.

see: the first few topics at this site
http://www.rogersaccesslibrary.com/forum/topic238.html

 

[anugroho]

Hi Jdraw,

The participants table is in order (linking correctly with the training tbl) and working as expected. This is a typical relationship participant - training - trainer.

My only issue is with this training - trainer relationship. Eventhough, this is a typical relationship participant - training - trainer, things get complicated (in db design), when i have to assign score individually to those trainers and track their performance.

The structure is basically what I can think of with my limited knowledge, so I need a really big help in building a sound structure... and I thank you for those links.. If you have some idea how the structure should be, would be great!!

Cheers

 

[stopher]

As jdraw says, your structure is wrong. It is a bad idea to store multiple filelds like you suggest.

Take a look at the attached. A many-many relationship is normally resolved using a third table (often called the junction table). This third table often represents the action (the record of something being done).

So I've created a table called tblAttendance. Take a look at the relationship view. Once you have this table it is easy to add fields about attendance e.g. score i.e. a trainer attending a given Training scores a given number.

You should not try to store averages. I've created a query that calculates averages by Trainer. You can use this whenever you need to report averages.

hth
Chris

 

[anugroho]

Hi Chris,
Wow thanks very much!!!!! I think this is the solution!!