training records (part 2)

S

scubadiver

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.
 
B

Bruce Meneghin

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)
 
K

KARL DEWEY

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.
 
T

TonyT

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 :p
 
S

scubadiver

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

scubadiver

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
 
S

scubadiver

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
 
T

TonyT

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..
 
S

scubadiver

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?
 
T

TonyT

Hi,

scubadiver said:
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 :p

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

TonyT..

TonyT..
 
S

scubadiver

I have only 6 months experience of db design. I am not saying this is way
over my head but it certainly isn't easy.

the whole point of prefixing some of the tables with "lkup_" is that it is
exactly what they would be used for: to provide a source for cascading combo
boxes in the form

As I said in my original message

Each course has many supervisors (potentially)
Each supervisor has many courses (potentially)

But also,

(1) the supervisor is also an employee (which you realise anyway)
(2) the training is done whenever required (I am not yet aware that training
will be done on fixed times on fixed dates)

also,

I need to trace dept and subdept for the trainee as well as the supervisor.

Probably not a very constructive response but I don't want to have to make
it more complicated than it has to be otherwise I wouldn't have a clue where
to start with designing the forms.

TonyT said:
Hi,

scubadiver said:
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 :p

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

TonyT..

TonyT..
 
T

TonyT

Hi again scuba,

replies in order below;

scubadiver said:
I have only 6 months experience of db design. I am not saying this is way
over my head but it certainly isn't easy.
We've all got to start somewhere! :p
the whole point of prefixing some of the tables with "lkup_" is that it is
exactly what they would be used for: to provide a source for cascading combo
boxes in the form

Most common naming conventions use just tbl & Name, with some adding Lnk or
Lkup at end to identify link tables and lookup tables, but 2 most important
things are YOU being able to easily understand them (if so others should too)
& that they are named consistently.
As I said in my original message

Each course has many supervisors (potentially)
Each supervisor has many courses (potentially)
I am still unsure as to whether the supervisor is an employee in charge of a
sub-department, or in charge of part of a sub-department, or responsible for
training for one or more sub-departments or even departments, or just
organises courses irrespective of department / sub department. <- Identifying
the role of the supervisor is the key to how the tables should be designed.
The way I understood it, (and you haven't fully clarified it yet so I may be
miles off the mark) a supervisor is answerable to a sub-department, with
employees answerable to him/her, who has many responsibilties, ONE of which
is to ensure employee's are trained as and when required. So on that
assumption alone I recommended the changes, you'll have to tell me if that
assumption is wrong :p
But also,

(1) the supervisor is also an employee (which you realise anyway)

see above for my confusion :/
(2) the training is done whenever required (I am not yet aware that training
will be done on fixed times on fixed dates)

In which case course offering is NOT an entity, and your original design for
having all course related info in one table is sound.
also,

I need to trace dept and subdept for the trainee as well as the supervisor.
You can, if supervisor is a level of management betweenSubDept and Employee
(as I assumed), then the SuperID(FK) in tbl_Employee will give you that
information thru' tbl_Super>lkup_SubDept>lkup_Dept.

In your original design you had Dept and SubDept in tbl_employee, this would
enable a user to change the Dept for an employee to something other than the
dept to which their sub-dept belonged, yet still have subDept pointing to a
subDept with the original (Different) Dept 'above' it, two different queries
of the same information could give 2 different results = BAD. Resolve the
heirachy thru the link tables to establish who belongs to what.
Probably not a very constructive response but I don't want to have to make
it more complicated than it has to be otherwise I wouldn't have a clue where
to start with designing the forms.

Help me to understand the role of supervisor better, and it may be that you
were right to do it your way from the beginning. I can only try and help
based on the information you have given me, you are doing exactly the right
way though - sort the table design first and do it only once!!

TonyT..
TonyT said:
Hi,

scubadiver said:
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 :p

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

TonyT..

TonyT..
:

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..

:

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

:

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 :p

:

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.
 
S

scubadiver

The team manager is an employee in charge of a sub-department but has to give
induction training to all members of staff regarding the manager's own
department.

However, there maybe general courses (as well job-specific courses) that may
benefit any employee (such as personal development or general business
skills).

I hope this clears it up a bit.

As far as the design goes this is what I now have:

lkup_Dept
Dept (PK)

lkup_Subdept
Dept (FK)
Subdept (PK)

lkup_Crselink
subdept (FK)
CourseID (FK)

tble_course
CourseID (PK)
Coursetype
CourseName

tble_course has two branches, one for employee and one for supervisor:

tble_spvsrlink
CourseID (FK)
SupervisorID (FK)

tble_supervisor
SupervisorID (PK)
Name
Dept
Subdept

tble_detail
trainingdate
instructor
EmployeeID (FK)
CourseID (FK)

tble_employee
EmployeeID (PK)
Name
workstatus
Dept
Subdept

TonyT said:
Hi again scuba,

replies in order below;

scubadiver said:
I have only 6 months experience of db design. I am not saying this is way
over my head but it certainly isn't easy.
We've all got to start somewhere! :p
the whole point of prefixing some of the tables with "lkup_" is that it is
exactly what they would be used for: to provide a source for cascading combo
boxes in the form

Most common naming conventions use just tbl & Name, with some adding Lnk or
Lkup at end to identify link tables and lookup tables, but 2 most important
things are YOU being able to easily understand them (if so others should too)
& that they are named consistently.
As I said in my original message

Each course has many supervisors (potentially)
Each supervisor has many courses (potentially)
I am still unsure as to whether the supervisor is an employee in charge of a
sub-department, or in charge of part of a sub-department, or responsible for
training for one or more sub-departments or even departments, or just
organises courses irrespective of department / sub department. <- Identifying
the role of the supervisor is the key to how the tables should be designed.
The way I understood it, (and you haven't fully clarified it yet so I may be
miles off the mark) a supervisor is answerable to a sub-department, with
employees answerable to him/her, who has many responsibilties, ONE of which
is to ensure employee's are trained as and when required. So on that
assumption alone I recommended the changes, you'll have to tell me if that
assumption is wrong :p
But also,

(1) the supervisor is also an employee (which you realise anyway)

see above for my confusion :/
(2) the training is done whenever required (I am not yet aware that training
will be done on fixed times on fixed dates)

In which case course offering is NOT an entity, and your original design for
having all course related info in one table is sound.
also,

I need to trace dept and subdept for the trainee as well as the supervisor.
You can, if supervisor is a level of management betweenSubDept and Employee
(as I assumed), then the SuperID(FK) in tbl_Employee will give you that
information thru' tbl_Super>lkup_SubDept>lkup_Dept.

In your original design you had Dept and SubDept in tbl_employee, this would
enable a user to change the Dept for an employee to something other than the
dept to which their sub-dept belonged, yet still have subDept pointing to a
subDept with the original (Different) Dept 'above' it, two different queries
of the same information could give 2 different results = BAD. Resolve the
heirachy thru the link tables to establish who belongs to what.
Probably not a very constructive response but I don't want to have to make
it more complicated than it has to be otherwise I wouldn't have a clue where
to start with designing the forms.

Help me to understand the role of supervisor better, and it may be that you
were right to do it your way from the beginning. I can only try and help
based on the information you have given me, you are doing exactly the right
way though - sort the table design first and do it only once!!

TonyT..
TonyT said:
Hi,

:

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 :p

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

TonyT..

TonyT..


:

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..

:

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

:

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 :p

:

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.
 
S

scubadiver

6 months ago I started developing a database for holding working hours but it
is a 1:m relationship between two tables when it should m:m (slap on wrist!)
only because I didn't know any better but that is no excuse.

I have posted a new message but nobody has responded apart from myself. The
message is called "working hours database" and is still on the first page.
When we have sorted this one out could you have a look at my other one as
well?

The current design is the first set of descriptions in the first message.
That is where I want to start.

Cheers.

TonyT said:
Hi again scuba,

replies in order below;

scubadiver said:
I have only 6 months experience of db design. I am not saying this is way
over my head but it certainly isn't easy.
We've all got to start somewhere! :p
the whole point of prefixing some of the tables with "lkup_" is that it is
exactly what they would be used for: to provide a source for cascading combo
boxes in the form

Most common naming conventions use just tbl & Name, with some adding Lnk or
Lkup at end to identify link tables and lookup tables, but 2 most important
things are YOU being able to easily understand them (if so others should too)
& that they are named consistently.
As I said in my original message

Each course has many supervisors (potentially)
Each supervisor has many courses (potentially)
I am still unsure as to whether the supervisor is an employee in charge of a
sub-department, or in charge of part of a sub-department, or responsible for
training for one or more sub-departments or even departments, or just
organises courses irrespective of department / sub department. <- Identifying
the role of the supervisor is the key to how the tables should be designed.
The way I understood it, (and you haven't fully clarified it yet so I may be
miles off the mark) a supervisor is answerable to a sub-department, with
employees answerable to him/her, who has many responsibilties, ONE of which
is to ensure employee's are trained as and when required. So on that
assumption alone I recommended the changes, you'll have to tell me if that
assumption is wrong :p
But also,

(1) the supervisor is also an employee (which you realise anyway)

see above for my confusion :/
(2) the training is done whenever required (I am not yet aware that training
will be done on fixed times on fixed dates)

In which case course offering is NOT an entity, and your original design for
having all course related info in one table is sound.
also,

I need to trace dept and subdept for the trainee as well as the supervisor.
You can, if supervisor is a level of management betweenSubDept and Employee
(as I assumed), then the SuperID(FK) in tbl_Employee will give you that
information thru' tbl_Super>lkup_SubDept>lkup_Dept.

In your original design you had Dept and SubDept in tbl_employee, this would
enable a user to change the Dept for an employee to something other than the
dept to which their sub-dept belonged, yet still have subDept pointing to a
subDept with the original (Different) Dept 'above' it, two different queries
of the same information could give 2 different results = BAD. Resolve the
heirachy thru the link tables to establish who belongs to what.
Probably not a very constructive response but I don't want to have to make
it more complicated than it has to be otherwise I wouldn't have a clue where
to start with designing the forms.

Help me to understand the role of supervisor better, and it may be that you
were right to do it your way from the beginning. I can only try and help
based on the information you have given me, you are doing exactly the right
way though - sort the table design first and do it only once!!

TonyT..
TonyT said:
Hi,

:

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 :p

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

TonyT..

TonyT..


:

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..

:

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

:

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 :p

:

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.
 
S

scubadiver

induction training would be considered to a general course for every employee
so that could simplify it.

TonyT said:
Hi again scuba,

replies in order below;

scubadiver said:
I have only 6 months experience of db design. I am not saying this is way
over my head but it certainly isn't easy.
We've all got to start somewhere! :p
the whole point of prefixing some of the tables with "lkup_" is that it is
exactly what they would be used for: to provide a source for cascading combo
boxes in the form

Most common naming conventions use just tbl & Name, with some adding Lnk or
Lkup at end to identify link tables and lookup tables, but 2 most important
things are YOU being able to easily understand them (if so others should too)
& that they are named consistently.
As I said in my original message

Each course has many supervisors (potentially)
Each supervisor has many courses (potentially)
I am still unsure as to whether the supervisor is an employee in charge of a
sub-department, or in charge of part of a sub-department, or responsible for
training for one or more sub-departments or even departments, or just
organises courses irrespective of department / sub department. <- Identifying
the role of the supervisor is the key to how the tables should be designed.
The way I understood it, (and you haven't fully clarified it yet so I may be
miles off the mark) a supervisor is answerable to a sub-department, with
employees answerable to him/her, who has many responsibilties, ONE of which
is to ensure employee's are trained as and when required. So on that
assumption alone I recommended the changes, you'll have to tell me if that
assumption is wrong :p
But also,

(1) the supervisor is also an employee (which you realise anyway)

see above for my confusion :/
(2) the training is done whenever required (I am not yet aware that training
will be done on fixed times on fixed dates)

In which case course offering is NOT an entity, and your original design for
having all course related info in one table is sound.
also,

I need to trace dept and subdept for the trainee as well as the supervisor.
You can, if supervisor is a level of management betweenSubDept and Employee
(as I assumed), then the SuperID(FK) in tbl_Employee will give you that
information thru' tbl_Super>lkup_SubDept>lkup_Dept.

In your original design you had Dept and SubDept in tbl_employee, this would
enable a user to change the Dept for an employee to something other than the
dept to which their sub-dept belonged, yet still have subDept pointing to a
subDept with the original (Different) Dept 'above' it, two different queries
of the same information could give 2 different results = BAD. Resolve the
heirachy thru the link tables to establish who belongs to what.
Probably not a very constructive response but I don't want to have to make
it more complicated than it has to be otherwise I wouldn't have a clue where
to start with designing the forms.

Help me to understand the role of supervisor better, and it may be that you
were right to do it your way from the beginning. I can only try and help
based on the information you have given me, you are doing exactly the right
way though - sort the table design first and do it only once!!

TonyT..
TonyT said:
Hi,

:

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 :p

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

TonyT..

TonyT..


:

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..

:

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

:

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 :p

:

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.
 
T

TonyT

Hi,

Just a thought, but if you include your e-mail address (add No_Spam or
similar) and I'll send you a zipped a2k db with my thoughts on tbl layout and
relationships, along with a couple of joining queries if you like.

replies in order;

scubadiver said:
The team manager is an employee in charge of a sub-department but has to give
induction training to all members of staff regarding the manager's own
department.
Now I'm beginning to understand better, one last question about
supervisor/team manager, is there a one-to-one relationship betweeen manager
and sub-dept? if so have them in the same table, unless you foresee that
changing in the future.
However, there maybe general courses (as well job-specific courses) that may
benefit any employee (such as personal development or general business
skills).

Your latest design looks like it will handle that fine, except for the
course/subDept link tbl, I would only link on employee to cover induction and
general employee courses too.
I hope this clears it up a bit.

As far as the design goes this is what I now have:

lkup_Dept
Dept (PK)

lkup_Subdept
Dept (FK)
Subdept (PK)

lkup_Crselink
subdept (FK)
CourseID (FK)

tble_course
CourseID (PK)
Coursetype
CourseName

tble_course has two branches, one for employee and one for supervisor:

tble_spvsrlink
CourseID (FK)
SupervisorID (FK)

tble_supervisor
SupervisorID (PK)
Name
Dept
Subdept
remove Dept and use SubDeptID as FK
tble_detail
trainingdate
instructor
EmployeeID (FK)
CourseID (FK)

tble_employee
EmployeeID (PK)
Name
workstatus
Dept
Subdept
remove Dept and use SubDeptID as FK

you're getting there!!

TonyT..
TonyT said:
Hi again scuba,

replies in order below;

scubadiver said:
I have only 6 months experience of db design. I am not saying this is way
over my head but it certainly isn't easy.
We've all got to start somewhere! :p
the whole point of prefixing some of the tables with "lkup_" is that it is
exactly what they would be used for: to provide a source for cascading combo
boxes in the form

Most common naming conventions use just tbl & Name, with some adding Lnk or
Lkup at end to identify link tables and lookup tables, but 2 most important
things are YOU being able to easily understand them (if so others should too)
& that they are named consistently.
As I said in my original message

Each course has many supervisors (potentially)
Each supervisor has many courses (potentially)
I am still unsure as to whether the supervisor is an employee in charge of a
sub-department, or in charge of part of a sub-department, or responsible for
training for one or more sub-departments or even departments, or just
organises courses irrespective of department / sub department. <- Identifying
the role of the supervisor is the key to how the tables should be designed.
The way I understood it, (and you haven't fully clarified it yet so I may be
miles off the mark) a supervisor is answerable to a sub-department, with
employees answerable to him/her, who has many responsibilties, ONE of which
is to ensure employee's are trained as and when required. So on that
assumption alone I recommended the changes, you'll have to tell me if that
assumption is wrong :p
But also,

(1) the supervisor is also an employee (which you realise anyway)

see above for my confusion :/
(2) the training is done whenever required (I am not yet aware that training
will be done on fixed times on fixed dates)

In which case course offering is NOT an entity, and your original design for
having all course related info in one table is sound.
also,

I need to trace dept and subdept for the trainee as well as the supervisor.
You can, if supervisor is a level of management betweenSubDept and Employee
(as I assumed), then the SuperID(FK) in tbl_Employee will give you that
information thru' tbl_Super>lkup_SubDept>lkup_Dept.

In your original design you had Dept and SubDept in tbl_employee, this would
enable a user to change the Dept for an employee to something other than the
dept to which their sub-dept belonged, yet still have subDept pointing to a
subDept with the original (Different) Dept 'above' it, two different queries
of the same information could give 2 different results = BAD. Resolve the
heirachy thru the link tables to establish who belongs to what.
Probably not a very constructive response but I don't want to have to make
it more complicated than it has to be otherwise I wouldn't have a clue where
to start with designing the forms.

Help me to understand the role of supervisor better, and it may be that you
were right to do it your way from the beginning. I can only try and help
based on the information you have given me, you are doing exactly the right
way though - sort the table design first and do it only once!!

TonyT..
:

Hi,

:

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 :p

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

TonyT..

TonyT..


:

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..

:

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

:

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 :p

:

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)
 
S

scubadiver

cheers!

(e-mail address removed)

NO spam or messages of a commercial nature PLEASE!

TonyT said:
Hi,

Just a thought, but if you include your e-mail address (add No_Spam or
similar) and I'll send you a zipped a2k db with my thoughts on tbl layout and
relationships, along with a couple of joining queries if you like.

replies in order;

scubadiver said:
The team manager is an employee in charge of a sub-department but has to give
induction training to all members of staff regarding the manager's own
department.
Now I'm beginning to understand better, one last question about
supervisor/team manager, is there a one-to-one relationship betweeen manager
and sub-dept? if so have them in the same table, unless you foresee that
changing in the future.
However, there maybe general courses (as well job-specific courses) that may
benefit any employee (such as personal development or general business
skills).

Your latest design looks like it will handle that fine, except for the
course/subDept link tbl, I would only link on employee to cover induction and
general employee courses too.
I hope this clears it up a bit.

As far as the design goes this is what I now have:

lkup_Dept
Dept (PK)

lkup_Subdept
Dept (FK)
Subdept (PK)

lkup_Crselink
subdept (FK)
CourseID (FK)

tble_course
CourseID (PK)
Coursetype
CourseName

tble_course has two branches, one for employee and one for supervisor:

tble_spvsrlink
CourseID (FK)
SupervisorID (FK)

tble_supervisor
SupervisorID (PK)
Name
Dept
Subdept
remove Dept and use SubDeptID as FK
tble_detail
trainingdate
instructor
EmployeeID (FK)
CourseID (FK)

tble_employee
EmployeeID (PK)
Name
workstatus
Dept
Subdept
remove Dept and use SubDeptID as FK

you're getting there!!

TonyT..
TonyT said:
Hi again scuba,

replies in order below;

:

I have only 6 months experience of db design. I am not saying this is way
over my head but it certainly isn't easy.

We've all got to start somewhere! :p

the whole point of prefixing some of the tables with "lkup_" is that it is
exactly what they would be used for: to provide a source for cascading combo
boxes in the form

Most common naming conventions use just tbl & Name, with some adding Lnk or
Lkup at end to identify link tables and lookup tables, but 2 most important
things are YOU being able to easily understand them (if so others should too)
& that they are named consistently.

As I said in my original message

Each course has many supervisors (potentially)
Each supervisor has many courses (potentially)

I am still unsure as to whether the supervisor is an employee in charge of a
sub-department, or in charge of part of a sub-department, or responsible for
training for one or more sub-departments or even departments, or just
organises courses irrespective of department / sub department. <- Identifying
the role of the supervisor is the key to how the tables should be designed.
The way I understood it, (and you haven't fully clarified it yet so I may be
miles off the mark) a supervisor is answerable to a sub-department, with
employees answerable to him/her, who has many responsibilties, ONE of which
is to ensure employee's are trained as and when required. So on that
assumption alone I recommended the changes, you'll have to tell me if that
assumption is wrong :p

But also,

(1) the supervisor is also an employee (which you realise anyway)

see above for my confusion :/

(2) the training is done whenever required (I am not yet aware that training
will be done on fixed times on fixed dates)

In which case course offering is NOT an entity, and your original design for
having all course related info in one table is sound.

also,

I need to trace dept and subdept for the trainee as well as the supervisor.

You can, if supervisor is a level of management betweenSubDept and Employee
(as I assumed), then the SuperID(FK) in tbl_Employee will give you that
information thru' tbl_Super>lkup_SubDept>lkup_Dept.

In your original design you had Dept and SubDept in tbl_employee, this would
enable a user to change the Dept for an employee to something other than the
dept to which their sub-dept belonged, yet still have subDept pointing to a
subDept with the original (Different) Dept 'above' it, two different queries
of the same information could give 2 different results = BAD. Resolve the
heirachy thru the link tables to establish who belongs to what.

Probably not a very constructive response but I don't want to have to make
it more complicated than it has to be otherwise I wouldn't have a clue where
to start with designing the forms.

Help me to understand the role of supervisor better, and it may be that you
were right to do it your way from the beginning. I can only try and help
based on the information you have given me, you are doing exactly the right
way though - sort the table design first and do it only once!!

TonyT..

:

Hi,

:

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 :p

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

TonyT..

TonyT..


:

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..

:

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

:

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..
 
S

scubadiver

I have just noticed that you suggest taking out "dept" and substituting
"subdept" as FK for the employee and supervisor tables. Are you suggesting
using the subdept field in "lkup_subdept" as the PK?

TonyT said:
Hi,

Just a thought, but if you include your e-mail address (add No_Spam or
similar) and I'll send you a zipped a2k db with my thoughts on tbl layout and
relationships, along with a couple of joining queries if you like.

replies in order;

scubadiver said:
The team manager is an employee in charge of a sub-department but has to give
induction training to all members of staff regarding the manager's own
department.
Now I'm beginning to understand better, one last question about
supervisor/team manager, is there a one-to-one relationship betweeen manager
and sub-dept? if so have them in the same table, unless you foresee that
changing in the future.
However, there maybe general courses (as well job-specific courses) that may
benefit any employee (such as personal development or general business
skills).

Your latest design looks like it will handle that fine, except for the
course/subDept link tbl, I would only link on employee to cover induction and
general employee courses too.
I hope this clears it up a bit.

As far as the design goes this is what I now have:

lkup_Dept
Dept (PK)

lkup_Subdept
Dept (FK)
Subdept (PK)

lkup_Crselink
subdept (FK)
CourseID (FK)

tble_course
CourseID (PK)
Coursetype
CourseName

tble_course has two branches, one for employee and one for supervisor:

tble_spvsrlink
CourseID (FK)
SupervisorID (FK)

tble_supervisor
SupervisorID (PK)
Name
Dept
Subdept
remove Dept and use SubDeptID as FK
tble_detail
trainingdate
instructor
EmployeeID (FK)
CourseID (FK)

tble_employee
EmployeeID (PK)
Name
workstatus
Dept
Subdept
remove Dept and use SubDeptID as FK

you're getting there!!

TonyT..
TonyT said:
Hi again scuba,

replies in order below;

:

I have only 6 months experience of db design. I am not saying this is way
over my head but it certainly isn't easy.

We've all got to start somewhere! :p

the whole point of prefixing some of the tables with "lkup_" is that it is
exactly what they would be used for: to provide a source for cascading combo
boxes in the form

Most common naming conventions use just tbl & Name, with some adding Lnk or
Lkup at end to identify link tables and lookup tables, but 2 most important
things are YOU being able to easily understand them (if so others should too)
& that they are named consistently.

As I said in my original message

Each course has many supervisors (potentially)
Each supervisor has many courses (potentially)

I am still unsure as to whether the supervisor is an employee in charge of a
sub-department, or in charge of part of a sub-department, or responsible for
training for one or more sub-departments or even departments, or just
organises courses irrespective of department / sub department. <- Identifying
the role of the supervisor is the key to how the tables should be designed.
The way I understood it, (and you haven't fully clarified it yet so I may be
miles off the mark) a supervisor is answerable to a sub-department, with
employees answerable to him/her, who has many responsibilties, ONE of which
is to ensure employee's are trained as and when required. So on that
assumption alone I recommended the changes, you'll have to tell me if that
assumption is wrong :p

But also,

(1) the supervisor is also an employee (which you realise anyway)

see above for my confusion :/

(2) the training is done whenever required (I am not yet aware that training
will be done on fixed times on fixed dates)

In which case course offering is NOT an entity, and your original design for
having all course related info in one table is sound.

also,

I need to trace dept and subdept for the trainee as well as the supervisor.

You can, if supervisor is a level of management betweenSubDept and Employee
(as I assumed), then the SuperID(FK) in tbl_Employee will give you that
information thru' tbl_Super>lkup_SubDept>lkup_Dept.

In your original design you had Dept and SubDept in tbl_employee, this would
enable a user to change the Dept for an employee to something other than the
dept to which their sub-dept belonged, yet still have subDept pointing to a
subDept with the original (Different) Dept 'above' it, two different queries
of the same information could give 2 different results = BAD. Resolve the
heirachy thru the link tables to establish who belongs to what.

Probably not a very constructive response but I don't want to have to make
it more complicated than it has to be otherwise I wouldn't have a clue where
to start with designing the forms.

Help me to understand the role of supervisor better, and it may be that you
were right to do it your way from the beginning. I can only try and help
based on the information you have given me, you are doing exactly the right
way though - sort the table design first and do it only once!!

TonyT..

:

Hi,

:

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 :p

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

TonyT..

TonyT..


:

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..

:

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

:

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..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top