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