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
Normalisation
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Normalisation
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Normalisation
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?
Do you want to keep a history of the roles an employee has had over time?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Normalisation
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.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Normalisation
I'd create a separate table to store the roles an employee has had:
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.
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.
You do not have the required permissions to view the files attached to this post.
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Normalisation
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
I think with that, the other tables should work OK (fingers crossed)
Thank you
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Normalisation
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?????
I need to tie in the Roles with Courses.
I've just knocked together the basic tables & relationships. Do these make sense?????
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Normalisation
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Normalisation
Good point, I was thinking of retaining the dates of previous versions, but realised this wasn't necessary.
Thank you
Thank you