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 |
|
#1
|
|||
|
|||
Many-2-many relationships: Can I be told ...
....what I am missing?
If I have training courses and employees, I know that each employee attends many training courses and each course is attended by many employees. That I can understand. If I set up a "1:n" relationship between "employee" and "course" I will know by DEFAULT who attended what course. Since I am assuming that this is the purpose of having a "1:n" relationship between "course" and "employee" doesn't this make the 2nd relationship completely redundant? I could be entirely wrong ... *sigh!* |
#2
|
|||
|
|||
Many-2-many relationships: Can I be told ...
The other important piece of the puzzle is that each course can be run
multiple times. Therefore employees enrol in an instance of a unit (e.g. the 2006 instance of "Basic Firefighting"), not just a unit Therefore you need: Unit table: one record for each subject that an employee can do: UnitID primary key Course table: One record for every time a Unit is offered CourseID primary key UnitID foreign key to Unit.UnitID StartDate when this instance of this unit begins. Enrol table: One record for every student in a course. EnrolID Primary key CourseID Which couse the person enrolled in. (Defines Unit too.) EmployeeID Who enrolled in this course. Outcome Whether the employee met all criteria Employee table: One record for each person EmployeeID primary key The Enrol table above resolves the many-to-many relationship between Course and Employee into a pair of one-to-many. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "scubadiver" wrote in message ... ...what I am missing? If I have training courses and employees, I know that each employee attends many training courses and each course is attended by many employees. That I can understand. If I set up a "1:n" relationship between "employee" and "course" I will know by DEFAULT who attended what course. Since I am assuming that this is the purpose of having a "1:n" relationship between "course" and "employee" doesn't this make the 2nd relationship completely redundant? I could be entirely wrong ... *sigh!* |
#3
|
|||
|
|||
Many-2-many relationships: Can I be told ...
I realise each course can be run multiple times.
In my database, I have a "1:n" relationship between "employee" and "course". I can select in the "course subform" all the training courses that each individual employee does. whats wrong with that? "Allen Browne" wrote: The other important piece of the puzzle is that each course can be run multiple times. Therefore employees enrol in an instance of a unit (e.g. the 2006 instance of "Basic Firefighting"), not just a unit Therefore you need: Unit table: one record for each subject that an employee can do: UnitID primary key Course table: One record for every time a Unit is offered CourseID primary key UnitID foreign key to Unit.UnitID StartDate when this instance of this unit begins. Enrol table: One record for every student in a course. EnrolID Primary key CourseID Which couse the person enrolled in. (Defines Unit too.) EmployeeID Who enrolled in this course. Outcome Whether the employee met all criteria Employee table: One record for each person EmployeeID primary key The Enrol table above resolves the many-to-many relationship between Course and Employee into a pair of one-to-many. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "scubadiver" wrote in message ... ...what I am missing? If I have training courses and employees, I know that each employee attends many training courses and each course is attended by many employees. That I can understand. If I set up a "1:n" relationship between "employee" and "course" I will know by DEFAULT who attended what course. Since I am assuming that this is the purpose of having a "1:n" relationship between "course" and "employee" doesn't this make the 2nd relationship completely redundant? I could be entirely wrong ... *sigh!* |
#4
|
|||
|
|||
Many-2-many relationships: Can I be told ...
If you have a one-to-many relation between employee and course, you have an
EmployeeID field in your Course table. Therefore one employee can do multiple courses, but any course can have only one employee. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "scubadiver" wrote in message ... I realise each course can be run multiple times. In my database, I have a "1:n" relationship between "employee" and "course". I can select in the "course subform" all the training courses that each individual employee does. whats wrong with that? "Allen Browne" wrote: The other important piece of the puzzle is that each course can be run multiple times. Therefore employees enrol in an instance of a unit (e.g. the 2006 instance of "Basic Firefighting"), not just a unit Therefore you need: Unit table: one record for each subject that an employee can do: UnitID primary key Course table: One record for every time a Unit is offered CourseID primary key UnitID foreign key to Unit.UnitID StartDate when this instance of this unit begins. Enrol table: One record for every student in a course. EnrolID Primary key CourseID Which couse the person enrolled in. (Defines Unit too.) EmployeeID Who enrolled in this course. Outcome Whether the employee met all criteria Employee table: One record for each person EmployeeID primary key The Enrol table above resolves the many-to-many relationship between Course and Employee into a pair of one-to-many. "scubadiver" wrote in message ... ...what I am missing? If I have training courses and employees, I know that each employee attends many training courses and each course is attended by many employees. That I can understand. If I set up a "1:n" relationship between "employee" and "course" I will know by DEFAULT who attended what course. Since I am assuming that this is the purpose of having a "1:n" relationship between "course" and "employee" doesn't this make the 2nd relationship completely redundant? I could be entirely wrong ... *sigh!* |
#5
|
|||
|
|||
Many-2-many relationships: Can I be told ...
Im being quite presumptious because the user won't get feedback from the
managers until next week! umm... "Allen Browne" wrote: If you have a one-to-many relation between employee and course, you have an EmployeeID field in your Course table. Therefore one employee can do multiple courses, but any course can have only one employee. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "scubadiver" wrote in message ... I realise each course can be run multiple times. In my database, I have a "1:n" relationship between "employee" and "course". I can select in the "course subform" all the training courses that each individual employee does. whats wrong with that? "Allen Browne" wrote: The other important piece of the puzzle is that each course can be run multiple times. Therefore employees enrol in an instance of a unit (e.g. the 2006 instance of "Basic Firefighting"), not just a unit Therefore you need: Unit table: one record for each subject that an employee can do: UnitID primary key Course table: One record for every time a Unit is offered CourseID primary key UnitID foreign key to Unit.UnitID StartDate when this instance of this unit begins. Enrol table: One record for every student in a course. EnrolID Primary key CourseID Which couse the person enrolled in. (Defines Unit too.) EmployeeID Who enrolled in this course. Outcome Whether the employee met all criteria Employee table: One record for each person EmployeeID primary key The Enrol table above resolves the many-to-many relationship between Course and Employee into a pair of one-to-many. "scubadiver" wrote in message ... ...what I am missing? If I have training courses and employees, I know that each employee attends many training courses and each course is attended by many employees. That I can understand. If I set up a "1:n" relationship between "employee" and "course" I will know by DEFAULT who attended what course. Since I am assuming that this is the purpose of having a "1:n" relationship between "course" and "employee" doesn't this make the 2nd relationship completely redundant? I could be entirely wrong ... *sigh!* |
#6
|
|||
|
|||
Many-2-many relationships: Can I be told ...
But that doesn't mean to say I can't list all the employees who have taken a
specific course? If establishing a 1:n relationship between course and employee means it could be quicker to enter information into a form, then there is a trade off. "Allen Browne" wrote: If you have a one-to-many relation between employee and course, you have an EmployeeID field in your Course table. Therefore one employee can do multiple courses, but any course can have only one employee. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "scubadiver" wrote in message ... I realise each course can be run multiple times. In my database, I have a "1:n" relationship between "employee" and "course". I can select in the "course subform" all the training courses that each individual employee does. whats wrong with that? "Allen Browne" wrote: The other important piece of the puzzle is that each course can be run multiple times. Therefore employees enrol in an instance of a unit (e.g. the 2006 instance of "Basic Firefighting"), not just a unit Therefore you need: Unit table: one record for each subject that an employee can do: UnitID primary key Course table: One record for every time a Unit is offered CourseID primary key UnitID foreign key to Unit.UnitID StartDate when this instance of this unit begins. Enrol table: One record for every student in a course. EnrolID Primary key CourseID Which couse the person enrolled in. (Defines Unit too.) EmployeeID Who enrolled in this course. Outcome Whether the employee met all criteria Employee table: One record for each person EmployeeID primary key The Enrol table above resolves the many-to-many relationship between Course and Employee into a pair of one-to-many. "scubadiver" wrote in message ... ...what I am missing? If I have training courses and employees, I know that each employee attends many training courses and each course is attended by many employees. That I can understand. If I set up a "1:n" relationship between "employee" and "course" I will know by DEFAULT who attended what course. Since I am assuming that this is the purpose of having a "1:n" relationship between "course" and "employee" doesn't this make the 2nd relationship completely redundant? I could be entirely wrong ... *sigh!* |
#7
|
|||
|
|||
Many-2-many relationships: Can I be told ...
Clearly, I have no idea what you are talking about either.
If your tables have no foreign keys, you can do what you like. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "scubadiver" wrote in message ... But that doesn't mean to say I can't list all the employees who have taken a specific course? If establishing a 1:n relationship between course and employee means it could be quicker to enter information into a form, then there is a trade off. |
#8
|
|||
|
|||
Many-2-many relationships: Can I be told ...
You could do it all in one table:
course, course date, course leader, employee name, employee number, employee phone You could extract all of the information that you require from that one table. There would be a tremendous amount of data duplication. That is why we have relational databases. In such a relational database the course table has all the informations about a course. The employee has all of the information about the employee. and the link table has all of the information about the relationship between the employee and the course. It might have contain date enlisted, the fee paid, the student grade for that course. three tables. "scubadiver" wrote in message ... But that doesn't mean to say I can't list all the employees who have taken a specific course? If establishing a 1:n relationship between course and employee means it could be quicker to enter information into a form, then there is a trade off. "Allen Browne" wrote: If you have a one-to-many relation between employee and course, you have an EmployeeID field in your Course table. Therefore one employee can do multiple courses, but any course can have only one employee. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "scubadiver" wrote in message ... I realise each course can be run multiple times. In my database, I have a "1:n" relationship between "employee" and "course". I can select in the "course subform" all the training courses that each individual employee does. whats wrong with that? "Allen Browne" wrote: The other important piece of the puzzle is that each course can be run multiple times. Therefore employees enrol in an instance of a unit (e.g. the 2006 instance of "Basic Firefighting"), not just a unit Therefore you need: Unit table: one record for each subject that an employee can do: UnitID primary key Course table: One record for every time a Unit is offered CourseID primary key UnitID foreign key to Unit.UnitID StartDate when this instance of this unit begins. Enrol table: One record for every student in a course. EnrolID Primary key CourseID Which couse the person enrolled in. (Defines Unit too.) EmployeeID Who enrolled in this course. Outcome Whether the employee met all criteria Employee table: One record for each person EmployeeID primary key The Enrol table above resolves the many-to-many relationship between Course and Employee into a pair of one-to-many. "scubadiver" wrote in message ... ...what I am missing? If I have training courses and employees, I know that each employee attends many training courses and each course is attended by many employees. That I can understand. If I set up a "1:n" relationship between "employee" and "course" I will know by DEFAULT who attended what course. Since I am assuming that this is the purpose of having a "1:n" relationship between "course" and "employee" doesn't this make the 2nd relationship completely redundant? I could be entirely wrong ... *sigh!* |
#9
|
|||
|
|||
Many-2-many relationships: Can I be told ...
It isn't as though we have set annual time tables like a professional
training organisation would have. The training where I work is done on an ad-hoc basis and whenever its required so I will include a date field to say when the employee did that course. I had a discussion with BruceM which turned rather heated but it was my fault for not being clearer than I should have been. "Allen Browne" wrote: The other important piece of the puzzle is that each course can be run multiple times. Therefore employees enrol in an instance of a unit (e.g. the 2006 instance of "Basic Firefighting"), not just a unit Therefore you need: Unit table: one record for each subject that an employee can do: UnitID primary key Course table: One record for every time a Unit is offered CourseID primary key UnitID foreign key to Unit.UnitID StartDate when this instance of this unit begins. Enrol table: One record for every student in a course. EnrolID Primary key CourseID Which couse the person enrolled in. (Defines Unit too.) EmployeeID Who enrolled in this course. Outcome Whether the employee met all criteria Employee table: One record for each person EmployeeID primary key The Enrol table above resolves the many-to-many relationship between Course and Employee into a pair of one-to-many. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "scubadiver" wrote in message ... ...what I am missing? If I have training courses and employees, I know that each employee attends many training courses and each course is attended by many employees. That I can understand. If I set up a "1:n" relationship between "employee" and "course" I will know by DEFAULT who attended what course. Since I am assuming that this is the purpose of having a "1:n" relationship between "course" and "employee" doesn't this make the 2nd relationship completely redundant? I could be entirely wrong ... *sigh!* |
#10
|
|||
|
|||
Many-2-many relationships: Can I be told ...
yes, i followed that thread. the issue is not how clear your descriptions
are, hon. you seem to think that if you explain your entities enough, someone will tell you that they are not a many-to-many relationship and you don't need a junction table. but the fact is that employees and courses IS a many-to-many relationship, and you DO need a third, junction table to model that relationship. hth "scubadiver" wrote in message ... It isn't as though we have set annual time tables like a professional training organisation would have. The training where I work is done on an ad-hoc basis and whenever its required so I will include a date field to say when the employee did that course. I had a discussion with BruceM which turned rather heated but it was my fault for not being clearer than I should have been. "Allen Browne" wrote: The other important piece of the puzzle is that each course can be run multiple times. Therefore employees enrol in an instance of a unit (e.g. the 2006 instance of "Basic Firefighting"), not just a unit Therefore you need: Unit table: one record for each subject that an employee can do: UnitID primary key Course table: One record for every time a Unit is offered CourseID primary key UnitID foreign key to Unit.UnitID StartDate when this instance of this unit begins. Enrol table: One record for every student in a course. EnrolID Primary key CourseID Which couse the person enrolled in. (Defines Unit too.) EmployeeID Who enrolled in this course. Outcome Whether the employee met all criteria Employee table: One record for each person EmployeeID primary key The Enrol table above resolves the many-to-many relationship between Course and Employee into a pair of one-to-many. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "scubadiver" wrote in message ... ...what I am missing? If I have training courses and employees, I know that each employee attends many training courses and each course is attended by many employees. That I can understand. If I set up a "1:n" relationship between "employee" and "course" I will know by DEFAULT who attended what course. Since I am assuming that this is the purpose of having a "1:n" relationship between "course" and "employee" doesn't this make the 2nd relationship completely redundant? I could be entirely wrong ... *sigh!* |
|
Thread Tools | |
Display Modes | |
|
|