Normalisation

[Egg 'n' Bacon]

I'm really struggling with (what I thought and probably is) a pretty basic training records DB; I just can't seem to get away from duplicate entries & coould do with a fresh viewpoint.

The DB is based upon the following statements;
* Each employee has a Role
* Employees can change Roles
* Each Role has a list of required skills
* Each skill can be updated to later versions
* Dates of training must be recorded (course, version, etc)

As you can see I've made a start on table creation & filling in some sample data, but I'm just not convinced it'll work with this set-up.

Anyone got some ideas that could help?

TIA

[HansV]

Does each employee have a single role (at a time), or can an employee have several roles?

Do you want to keep a history of the roles an employee has had over time?

[Egg 'n' Bacon]

For the purpose of these records, an employee has a single role at a time and we would like to keep records of when an employee was in any particular role.

[HansV]

I'd create a separate table to store the roles an employee has had:

x44.png

tblEmployees lists the information about employees such as name, date of birth, etc. Each employee has a single record in this table.
tblEmployeeRoles lists the roles an employee has had. One employee can have multiple records in this table.

[Egg 'n' Bacon]

That does look a bit better and tidies up some of the PK/FK issues too.

I think with that, the other tables should work OK (fingers crossed)

Thank you

[Egg 'n' Bacon]

Oops, thought I had it, but seems I was mistaken

I need to tie in the Roles with Courses.

I've just knocked together the basic tables & relationships. Do these make sense?????

Rel.JPG

[HansV]

Just like you did for tblPerson and tblRole, I would use an AutoNumber field CourseID as primary key for tblCourse, and then use CourseID to link to other tables. In your setup, you have to use two fields to link tblCourse.

[Egg 'n' Bacon]

Good point, I was thinking of retaining the dates of previous versions, but realised this wasn't necessary.

Thank you