If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
training records (part 2)
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" wrote: 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. |
#12
|
|||
|
|||
training records (part 2)
Hi again scuba,
replies in order below; "scubadiver" wrote: 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_Superlkup_SubDeptlkup_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" wrote: 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. |
#13
|
|||
|
|||
training records (part 2)
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" wrote: Hi again scuba, replies in order below; "scubadiver" wrote: 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_Superlkup_SubDeptlkup_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" wrote: 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. |
#14
|
|||
|
|||
training records (part 2)
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" wrote: Hi again scuba, replies in order below; "scubadiver" wrote: 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_Superlkup_SubDeptlkup_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" wrote: 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. |
#15
|
|||
|
|||
training records (part 2)
induction training would be considered to a general course for every employee
so that could simplify it. "TonyT" wrote: Hi again scuba, replies in order below; "scubadiver" wrote: 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_Superlkup_SubDeptlkup_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" wrote: 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. |
#16
|
|||
|
|||
training records (part 2)
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" wrote: 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" wrote: Hi again scuba, replies in order below; "scubadiver" wrote: 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_Superlkup_SubDeptlkup_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" wrote: 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) |
#17
|
|||
|
|||
training records (part 2)
|
#18
|
|||
|
|||
training records (part 2)
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" wrote: 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" wrote: 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" wrote: Hi again scuba, replies in order below; "scubadiver" wrote: 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_Superlkup_SubDeptlkup_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" wrote: 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.. |
|
Thread Tools | |
Display Modes | |
|
|