training records (part 2)

I've been sorting out my design for this and I *think* I have come up with
something that should pass the test! As far as I can see, none of the fields
or information is duplicated.

Please tell me if there is anything wrong with this (I hope it can be
understood!).
In words, here is what I currently have:

Each department has many subdepartments
Each subdepartment will have many courses
Each course can be in many subdepartments
Each course has many supervisors (potentially)
Each supervisor has many courses (potentially)
Each employee will attend many courses
Each course is attended by many employees


I have 7 tables:

lkup_Dept
Dept (PK)

lkup_Subdept
Dept (FK)
Subdept (PK)

lkup_crselink
Subdept (FK)
CourseID (FK)

tble_course
CourseID (PK)
Coursetype
CourseName

lkup_spvsrlink
CourseID (FK)
EmployeeID (FK)

tble_employee
EmployeeID (PK)
Name
Workstatus
Operations
Dept
Subdept
Supervisor

tble_detail
trainingdate
instructor
EmployeeID (FK)
CourseID (FK)

The main form that will be used to enter course information for each
supervisor will be a filtered employee table.

The tble_detail table covers both the "course offering" entity and the
enrollment for that "course offering". It would be more normal to split
those out

tble_detail
OfferingID(PK)
trainingdate
instructor
CourseID(FK)

tble_enrollment
OfferingID(FK)
EmployeeID(FK)

"scubadiver" wrote:

I've been sorting out my design for this and I *think* I have come up with
something that should pass the test! As far as I can see, none of the fields
or information is duplicated.

Please tell me if there is anything wrong with this (I hope it can be
understood!).
In words, here is what I currently have:

Each department has many subdepartments
Each subdepartment will have many courses
Each course can be in many subdepartments
Each course has many supervisors (potentially)
Each supervisor has many courses (potentially)
Each employee will attend many courses
Each course is attended by many employees


I have 7 tables:

lkup_Dept
Dept (PK)

lkup_Subdept
Dept (FK)
Subdept (PK)

lkup_crselink
Subdept (FK)
CourseID (FK)

tble_course
CourseID (PK)
Coursetype
CourseName

lkup_spvsrlink
CourseID (FK)
EmployeeID (FK)

tble_employee
EmployeeID (PK)
Name
Workstatus
Operations
Dept
Subdept
Supervisor

tble_detail
trainingdate
instructor
EmployeeID (FK)
CourseID (FK)

The main form that will be used to enter course information for each
supervisor will be a filtered employee table.

Some courses must be repeated at set intervals - annual safety training.

Add to your tble_course like this --
tble_course
CourseID (PK)
Coursetype
CourseName
Interval - Number - Integer (number of months)

Then you can use a query to determine when individual needs to receive the
training again.


"scubadiver" wrote:

I've been sorting out my design for this and I *think* I have come up with
something that should pass the test! As far as I can see, none of the fields
or information is duplicated.

Please tell me if there is anything wrong with this (I hope it can be
understood!).
In words, here is what I currently have:

Each department has many subdepartments
Each subdepartment will have many courses
Each course can be in many subdepartments
Each course has many supervisors (potentially)
Each supervisor has many courses (potentially)
Each employee will attend many courses
Each course is attended by many employees


I have 7 tables:

lkup_Dept
Dept (PK)

lkup_Subdept
Dept (FK)
Subdept (PK)

lkup_crselink
Subdept (FK)
CourseID (FK)

tble_course
CourseID (PK)
Coursetype
CourseName

lkup_spvsrlink
CourseID (FK)
EmployeeID (FK)

tble_employee
EmployeeID (PK)
Name
Workstatus
Operations
Dept
Subdept
Supervisor

tble_detail
trainingdate
instructor
EmployeeID (FK)
CourseID (FK)

The main form that will be used to enter course information for each
supervisor will be a filtered employee table.

Hi scubadiver,

Your lkup_spvlink table seems to be a course/employee link table, that has
supervisor in the employee table, does that mean that there is no
relationship between employee and supervisor? ie. is there 1 supervisor to
one employee and vice versa?
Also does the instructor belong to the course or the individual training
session? if it's the course they should be in the course table, or a table of
their own.

TonyT..

PS bet you didn't want to hear these various comments did you

"scubadiver" wrote:

I've been sorting out my design for this and I *think* I have come up with
something that should pass the test! As far as I can see, none of the fields
or information is duplicated.

Please tell me if there is anything wrong with this (I hope it can be
understood!).
In words, here is what I currently have:

Each department has many subdepartments
Each subdepartment will have many courses
Each course can be in many subdepartments
Each course has many supervisors (potentially)
Each supervisor has many courses (potentially)
Each employee will attend many courses
Each course is attended by many employees


I have 7 tables:

lkup_Dept
Dept (PK)

lkup_Subdept
Dept (FK)
Subdept (PK)

lkup_crselink
Subdept (FK)
CourseID (FK)

tble_course
CourseID (PK)
Coursetype
CourseName

lkup_spvsrlink
CourseID (FK)
EmployeeID (FK)

tble_employee
EmployeeID (PK)
Name
Workstatus
Operations
Dept
Subdept
Supervisor

tble_detail
trainingdate
instructor
EmployeeID (FK)
CourseID (FK)

The main form that will be used to enter course information for each
supervisor will be a filtered employee table.

The anticipated training will be job related but it is certainly something to
think about. I will mention it to the user.

"KARL DEWEY" wrote:

Some courses must be repeated at set intervals - annual safety training.

Add to your tble_course like this --
tble_course
CourseID (PK)
Coursetype
CourseName
Interval - Number - Integer (number of months)

Then you can use a query to determine when individual needs to receive the
training again.


"scubadiver" wrote:

I've been sorting out my design for this and I *think* I have come up with
something that should pass the test! As far as I can see, none of the fields
or information is duplicated.

Please tell me if there is anything wrong with this (I hope it can be
understood!).
In words, here is what I currently have:

Each department has many subdepartments
Each subdepartment will have many courses
Each course can be in many subdepartments
Each course has many supervisors (potentially)
Each supervisor has many courses (potentially)
Each employee will attend many courses
Each course is attended by many employees


I have 7 tables:

lkup_Dept
Dept (PK)

lkup_Subdept
Dept (FK)
Subdept (PK)

lkup_crselink
Subdept (FK)
CourseID (FK)

tble_course
CourseID (PK)
Coursetype
CourseName

lkup_spvsrlink
CourseID (FK)
EmployeeID (FK)

tble_employee
EmployeeID (PK)
Name
Workstatus
Operations
Dept
Subdept
Supervisor

tble_detail
trainingdate
instructor
EmployeeID (FK)
CourseID (FK)

The main form that will be used to enter course information for each
supervisor will be a filtered employee table.

There is a reason why did this and it is a bit complicated.

It was suggested to me that the list of supervisors can be taken directly
from the employees via a supervisor checkbox.

As far as employees go, I had a table for course details ("tble_course"), a
table for employee details ("tble_employee") and a junction table in the
middle ("tble_detail").

What I realised is that I had a copy of "tble_course" with another name
because I want to associate the course with the subdepartment and the
supervisor. It isn't worth having two tables with exactly the same fields so
I deleted one and changed the relationships.

As far as supervisors and courses is concerned I have a m:m relationship.
The main form will be the list of supervisors and this is taken from a query
sourced from the employee table. So the employee table has a second job (so
to speak).

In summary, "tble_course" is associated with three m:m relationships:

employee,
supervisor (whose details come from the employee table)
subdept

"TonyT" wrote:

Hi scubadiver,

Your lkup_spvlink table seems to be a course/employee link table, that has
supervisor in the employee table, does that mean that there is no
relationship between employee and supervisor? ie. is there 1 supervisor to
one employee and vice versa?
Also does the instructor belong to the course or the individual training
session? if it's the course they should be in the course table, or a table of
their own.

TonyT..

PS bet you didn't want to hear these various comments did you

"scubadiver" wrote:

I've been sorting out my design for this and I *think* I have come up with
something that should pass the test! As far as I can see, none of the fields
or information is duplicated.

Please tell me if there is anything wrong with this (I hope it can be
understood!).
In words, here is what I currently have:

Each department has many subdepartments
Each subdepartment will have many courses
Each course can be in many subdepartments
Each course has many supervisors (potentially)
Each supervisor has many courses (potentially)
Each employee will attend many courses
Each course is attended by many employees


I have 7 tables:

lkup_Dept
Dept (PK)

lkup_Subdept
Dept (FK)
Subdept (PK)

lkup_crselink
Subdept (FK)
CourseID (FK)

tble_course
CourseID (PK)
Coursetype
CourseName

lkup_spvsrlink
CourseID (FK)
EmployeeID (FK)

tble_employee
EmployeeID (PK)
Name
Workstatus
Operations
Dept
Subdept
Supervisor

tble_detail
trainingdate
instructor
EmployeeID (FK)
CourseID (FK)

The main form that will be used to enter course information for each
supervisor will be a filtered employee table.

At the moment, each employee can have many courses and each course can have
many employees.

What would the purpose be of the extra table?

thanks

"Bruce Meneghin" wrote:

The tble_detail table covers both the "course offering" entity and the
enrollment for that "course offering". It would be more normal to split
those out

tble_detail
OfferingID(PK)
trainingdate
instructor
CourseID(FK)

tble_enrollment
OfferingID(FK)
EmployeeID(FK)

"scubadiver" wrote:

I've been sorting out my design for this and I *think* I have come up with
something that should pass the test! As far as I can see, none of the fields
or information is duplicated.

Please tell me if there is anything wrong with this (I hope it can be
understood!).
In words, here is what I currently have:

Each department has many subdepartments
Each subdepartment will have many courses
Each course can be in many subdepartments
Each course has many supervisors (potentially)
Each supervisor has many courses (potentially)
Each employee will attend many courses
Each course is attended by many employees


I have 7 tables:

lkup_Dept
Dept (PK)

lkup_Subdept
Dept (FK)
Subdept (PK)

lkup_crselink
Subdept (FK)
CourseID (FK)

tble_course
CourseID (PK)
Coursetype
CourseName

lkup_spvsrlink
CourseID (FK)
EmployeeID (FK)

tble_employee
EmployeeID (PK)
Name
Workstatus
Operations
Dept
Subdept
Supervisor

tble_detail
trainingdate
instructor
EmployeeID (FK)
CourseID (FK)

The main form that will be used to enter course information for each
supervisor will be a filtered employee table.

Hi again,

I would still be inclined to separate out supervisor & possibly include
subdept in the same table, if the user suddenly asks 'have all the employess
of x supervisor/sub-department undertaken all the required training?' you've
got alot more work to do to find out, and the duplicated data along the way
in the 2 fields.
If you foresee even a vague reason why it might be a good reason to split
out the data, I would always do it if the information isn't relating to a
single entity - ie a single employee.

TonyT..

"scubadiver" wrote:

There is a reason why did this and it is a bit complicated.

It was suggested to me that the list of supervisors can be taken directly
from the employees via a supervisor checkbox.

As far as employees go, I had a table for course details ("tble_course"), a
table for employee details ("tble_employee") and a junction table in the
middle ("tble_detail").

What I realised is that I had a copy of "tble_course" with another name
because I want to associate the course with the subdepartment and the
supervisor. It isn't worth having two tables with exactly the same fields so
I deleted one and changed the relationships.

As far as supervisors and courses is concerned I have a m:m relationship.
The main form will be the list of supervisors and this is taken from a query
sourced from the employee table. So the employee table has a second job (so
to speak).

In summary, "tble_course" is associated with three m:m relationships:

employee,
supervisor (whose details come from the employee table)
subdept

"TonyT" wrote:

Hi scubadiver,

Your lkup_spvlink table seems to be a course/employee link table, that has
supervisor in the employee table, does that mean that there is no
relationship between employee and supervisor? ie. is there 1 supervisor to
one employee and vice versa?
Also does the instructor belong to the course or the individual training
session? if it's the course they should be in the course table, or a table of
their own.

TonyT..

PS bet you didn't want to hear these various comments did you

"scubadiver" wrote:

I've been sorting out my design for this and I *think* I have come up with
something that should pass the test! As far as I can see, none of the fields
or information is duplicated.

Please tell me if there is anything wrong with this (I hope it can be
understood!).
In words, here is what I currently have:

Each department has many subdepartments
Each subdepartment will have many courses
Each course can be in many subdepartments
Each course has many supervisors (potentially)
Each supervisor has many courses (potentially)
Each employee will attend many courses
Each course is attended by many employees


I have 7 tables:

lkup_Dept
Dept (PK)

lkup_Subdept
Dept (FK)
Subdept (PK)

lkup_crselink
Subdept (FK)
CourseID (FK)

tble_course
CourseID (PK)
Coursetype
CourseName

lkup_spvsrlink
CourseID (FK)
EmployeeID (FK)

tble_employee
EmployeeID (PK)
Name
Workstatus
Operations
Dept
Subdept
Supervisor

tble_detail
trainingdate
instructor
EmployeeID (FK)
CourseID (FK)

The main form that will be used to enter course information for each
supervisor will be a filtered employee table.

I have considered the question that you pose. The employees in a
sub-department are all doing the same job so they *should* all have the same
training requirements.

I have a relationship between subdepartment and course titles so I should be
able to use a query that lists courses that an employee hasn't done.

So are you suggesting that

(a) lkup_spvsrlink should act as a junction table between tble_course and
tble_supervisor

and

(b) tble_detail should act as a junction table between tble_course and
tble_employee

Also, that the [subdept] field should be in tble_supervisor as well as
tble_employee?



"TonyT" wrote:

Hi again,

I would still be inclined to separate out supervisor & possibly include
subdept in the same table, if the user suddenly asks 'have all the employess
of x supervisor/sub-department undertaken all the required training?' you've
got alot more work to do to find out, and the duplicated data along the way
in the 2 fields.
If you foresee even a vague reason why it might be a good reason to split
out the data, I would always do it if the information isn't relating to a
single entity - ie a single employee.

TonyT..

"scubadiver" wrote:

There is a reason why did this and it is a bit complicated.

It was suggested to me that the list of supervisors can be taken directly
from the employees via a supervisor checkbox.

As far as employees go, I had a table for course details ("tble_course"), a
table for employee details ("tble_employee") and a junction table in the
middle ("tble_detail").

What I realised is that I had a copy of "tble_course" with another name
because I want to associate the course with the subdepartment and the
supervisor. It isn't worth having two tables with exactly the same fields so
I deleted one and changed the relationships.

As far as supervisors and courses is concerned I have a m:m relationship.
The main form will be the list of supervisors and this is taken from a query
sourced from the employee table. So the employee table has a second job (so
to speak).

In summary, "tble_course" is associated with three m:m relationships:

employee,
supervisor (whose details come from the employee table)
subdept

"TonyT" wrote:

Hi scubadiver,

Your lkup_spvlink table seems to be a course/employee link table, that has
supervisor in the employee table, does that mean that there is no
relationship between employee and supervisor? ie. is there 1 supervisor to
one employee and vice versa?
Also does the instructor belong to the course or the individual training
session? if it's the course they should be in the course table, or a table of
their own.

TonyT..

PS bet you didn't want to hear these various comments did you

"scubadiver" wrote:

I've been sorting out my design for this and I *think* I have come up with
something that should pass the test! As far as I can see, none of the fields
or information is duplicated.

Please tell me if there is anything wrong with this (I hope it can be
understood!).
In words, here is what I currently have:

Each department has many subdepartments
Each subdepartment will have many courses
Each course can be in many subdepartments
Each course has many supervisors (potentially)
Each supervisor has many courses (potentially)
Each employee will attend many courses
Each course is attended by many employees


I have 7 tables:

lkup_Dept
Dept (PK)

lkup_Subdept
Dept (FK)
Subdept (PK)

lkup_crselink
Subdept (FK)
CourseID (FK)

tble_course
CourseID (PK)
Coursetype
CourseName

lkup_spvsrlink
CourseID (FK)
EmployeeID (FK)

tble_employee
EmployeeID (PK)
Name
Workstatus
Operations
Dept
Subdept
Supervisor

tble_detail
trainingdate
instructor
EmployeeID (FK)
CourseID (FK)

The main form that will be used to enter course information for each
supervisor will be a filtered employee table.

Hi,

"scubadiver" wrote:

I have considered the question that you pose. The employees in a
sub-department are all doing the same job so they *should* all have the same
training requirements.

I have a relationship between subdepartment and course titles so I should be
able to use a query that lists courses that an employee hasn't done.

Does each subdepartment only have 1 supervisor?, if so I would suggest
adding supervisor to lkup_subDept and just use SubDept as an FK in
tbl_employee, which means you should also do away with Dept in tbl_employee
(regardless of whether you make this change or not in fact), and use the
lkup_Subdept to lookup which dept the employee is in.
If not (1 supervisor per subDept) then see below ****

So are you suggesting that

(a) lkup_spvsrlink should act as a junction table between tble_course and
tble_supervisor

leave as is but rename to lkup_CourseEmplink


and

(b) tble_detail should act as a junction table between tble_course and
tble_employee

Yes, I beleive it should, all the course information, Instructor etc should
be in the tbl_Course, and the detail nothing more than showing who took which
course and when, unless the courses are available a different times with
different Instructors.

Also, that the [subdept] field should be in tble_supervisor as well as
tble_employee?

yes, see above.


**** more than 1 supervisor per sub department****
If this is the case, then I would suggest yet another table lkup_Super with:
SuperID (PK)
Super name etc
SubDept (FK)
linked one-to-many to lkup_Subdept
Then do away with Dept/SubDept/Supervisor from tbl_Employee and add
SuperID (FK) too tbl_employee and use that to trace subdept and dept.

Get to thinking more about things as 'entities' - eg a department is an
entity - it has a name, a floor or address and various other attributes, a
sub-department could also be considered as one of the departments attributes,
but then a sub-department has it's own employees, name, (poss supervisor) etc
etc, so that too is an entity with separate attributes to dept, but happens
to be linked to Dept.

So your list of actual entities looks like;
Department
Sub-Department
(Supervisor if more than 1 per sub-dept, otherwise an attribute of sub-dept)
Employee
the above are all inter-related in naturally cascading order downwards,
then you have;
Course available
Course undertaken
course avaliable stands in it's own right, unrelated to anything other than
course undertaken, just because it's there doesn't affect anybody or anything
else
Course undertaken is yet another entity with different
attributes(instructor/employee) even if it's the same course taken on a
different day.

Once you have all that determined, it becomes easier to organise the tables,
too many people start with too few tables and then go on to make loads of
unnecessary tables in their next design just because they did too few last
time

probably telling granny to suck eggs.......but hope it helped some more,

TonyT..

TonyT..


"TonyT" wrote:

Hi again,

I would still be inclined to separate out supervisor & possibly include
subdept in the same table, if the user suddenly asks 'have all the employess
of x supervisor/sub-department undertaken all the required training?' you've
got alot more work to do to find out, and the duplicated data along the way
in the 2 fields.
If you foresee even a vague reason why it might be a good reason to split
out the data, I would always do it if the information isn't relating to a
single entity - ie a single employee.

TonyT..

"scubadiver" wrote:

There is a reason why did this and it is a bit complicated.

It was suggested to me that the list of supervisors can be taken directly
from the employees via a supervisor checkbox.

As far as employees go, I had a table for course details ("tble_course"), a
table for employee details ("tble_employee") and a junction table in the
middle ("tble_detail").

What I realised is that I had a copy of "tble_course" with another name
because I want to associate the course with the subdepartment and the
supervisor. It isn't worth having two tables with exactly the same fields so
I deleted one and changed the relationships.

As far as supervisors and courses is concerned I have a m:m relationship.
The main form will be the list of supervisors and this is taken from a query
sourced from the employee table. So the employee table has a second job (so
to speak).

In summary, "tble_course" is associated with three m:m relationships:

employee,
supervisor (whose details come from the employee table)
subdept

"TonyT" wrote:

Hi scubadiver,

Your lkup_spvlink table seems to be a course/employee link table, that has
supervisor in the employee table, does that mean that there is no
relationship between employee and supervisor? ie. is there 1 supervisor to
one employee and vice versa?
Also does the instructor belong to the course or the individual training
session? if it's the course they should be in the course table, or a table of
their own.

TonyT..

PS bet you didn't want to hear these various comments did you

"scubadiver" wrote:

I've been sorting out my design for this and I *think* I have come up with
something that should pass the test! As far as I can see, none of the fields
or information is duplicated.

Please tell me if there is anything wrong with this (I hope it can be
understood!).
In words, here is what I currently have:

Each department has many subdepartments
Each subdepartment will have many courses
Each course can be in many subdepartments
Each course has many supervisors (potentially)
Each supervisor has many courses (potentially)
Each employee will attend many courses
Each course is attended by many employees


I have 7 tables:

lkup_Dept
Dept (PK)

lkup_Subdept
Dept (FK)
Subdept (PK)

lkup_crselink
Subdept (FK)
CourseID (FK)

tble_course
CourseID (PK)
Coursetype
CourseName

lkup_spvsrlink
CourseID (FK)
EmployeeID (FK)

tble_employee
EmployeeID (PK)
Name
Workstatus
Operations
Dept
Subdept
Supervisor

tble_detail
trainingdate
instructor
EmployeeID (FK)
CourseID (FK)

The main form that will be used to enter course information for each
supervisor will be a filtered employee table.