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
|
|||
|
|||
3rd tier level table
I have a training "events" style database and am adding a new table to record
the different presenters for each event I already have a "Training course" table, an "Everyone" table and a "Registrations" table that is related to both tables theough their primary keys Everyone ID & Training Course ID. I now want to add a "Presenters" table that will record the multiple speakers/presenters for each course. I will relate it to the Registrations table through Reg ID but should i also have fields Everyone ID & Training Course ID on my new table and relate them directly back to the orignal tables? How will it affect the creation of forms & queries etc in the future. |
#2
|
|||
|
|||
3rd tier level table
You should only relate your new table to the other tables if ONLY if a
real-world relationship exists. First question: Does the Presenter REALLY belong in the Registration table? It's possible, I suppose, but the Presenter doesn't usually have anything to do with the Registration. That's between the person and the course. Second: You most likely want to relate the Presenter table to the Training Course table instead. However, unless each presenter can present to one and ONLY one Course, you've really got a Many-to-Many relationship. In which case, you need another linking table (Course_Presenter) which will have the foreign keys PresenterID and TrainingCourseID. Thirdly: It looks as though there is only one session of each Course. If that's the case, then your design is fine, but if there are multiple sessions of each course, you'll either have to repeat a lot of course information in your table (bad idea) or create a Sessions table where each record represents a single session of a Course. On my website (www.rogersaccesslibrary.com), are several small Access databases samples that might be helpful. "TrainingRegistration.mdb" is a working application for registering students for training courses. On my Tutorials page: http://www.rogersaccesslibrary.com/TutorialsDesign.html there is a Design Tutorial called "Consolidated Widgets" which is a design for a "Widget" trade show that has multiple sessions and multiple presenters. Lastly, there is a sample called "ImplementingM2MRelationship.mdb" which might give you some help with your forms question. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Engels" wrote in message ... I have a training "events" style database and am adding a new table to record the different presenters for each event I already have a "Training course" table, an "Everyone" table and a "Registrations" table that is related to both tables theough their primary keys Everyone ID & Training Course ID. I now want to add a "Presenters" table that will record the multiple speakers/presenters for each course. I will relate it to the Registrations table through Reg ID but should i also have fields Everyone ID & Training Course ID on my new table and relate them directly back to the orignal tables? How will it affect the creation of forms & queries etc in the future. |
#3
|
|||
|
|||
3rd tier level table
Right that was really helpful Roger thankyou but can you clarify something
for me... but i'll go through the answere to your questions first.. First question: Does the Presenter REALLY belong in the Registration table? No you are right, it was perfectly possible to do it with the presenter in the same table but i have decided your way makes more sense. Second: You most likely want to relate the Presenter table to the Training Course table instead. However, unless each presenter can present to one and ONLY one Course, you've really got a Many-to-Many relationship. In which case, you need another linking table (Course_Presenter) which will have the foreign keys PresenterID and TrainingCourseID. I need a Many to Many relationship. Thirdly: It looks as though there is only one session of each Course. If that's the case, then your design is fine, but if there are multiple sessions of each course, you'll either have to repeat a lot of course information in your table (bad idea) or create a Sessions table where each record represents a single session of a Course. Right here's where i am getting confused. I will need to create a Sessions table as i will have mutiple presenters for multple courses each with multiple sessions with different presenters presenting more than one session but maybe even more than one presenter in each session. Now i am confused. how does that affect the tables & relationships i need to create. |
#4
|
|||
|
|||
3rd tier level table
In cases like these, it's helpful to write out your relationships in
sentences: Each Course can have One or More Sessions Each Session can be for One and Only one Course (This is a 1:M relationship. You'll put the CourseID in the Sessions table as a Foreign Key.) Each Person can take One or More Courses (BUT WAIT! This is not correct. People don't take Courses, they take a Session of a Courses. Let's rethink.) Each Person can take One or More Sessions Each Session can contain One or More People. (There. That's right now. Since this is a M:M relationship, you need a linking table. The Registration table will work for this.) Each Session can have One or More Presenters Each Presenter can present at One or More Session (Again, another M:M relationship, which needs a linking table) Therefore, your tables would look something like this: Everyone (Person) ======= EveryoneID (pk) (other fields) Registration ======== RegID (pk) EveryoneID (fk to Everyone) SessionID (fk to Session) (other fields) Session ====== SessionID (pk) CourseID (fk to Course) (other fields) Course ===== CourseID (pk) (other fields) Session/Presenter =========== SessionID (fk to Session) PresenterID (fk to Presenter) (other fields) Presenter ====== PresenterID (pk) (other fields) As I said earlier, you might find my "ImplementingM2MRelationship.mdb" sample helpful in implementing these relationship on a form. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Engels" wrote in message ... Right that was really helpful Roger thankyou but can you clarify something for me... but i'll go through the answere to your questions first.. First question: Does the Presenter REALLY belong in the Registration table? No you are right, it was perfectly possible to do it with the presenter in the same table but i have decided your way makes more sense. Second: You most likely want to relate the Presenter table to the Training Course table instead. However, unless each presenter can present to one and ONLY one Course, you've really got a Many-to-Many relationship. In which case, you need another linking table (Course_Presenter) which will have the foreign keys PresenterID and TrainingCourseID. I need a Many to Many relationship. Thirdly: It looks as though there is only one session of each Course. If that's the case, then your design is fine, but if there are multiple sessions of each course, you'll either have to repeat a lot of course information in your table (bad idea) or create a Sessions table where each record represents a single session of a Course. Right here's where i am getting confused. I will need to create a Sessions table as i will have mutiple presenters for multple courses each with multiple sessions with different presenters presenting more than one session but maybe even more than one presenter in each session. Now i am confused. how does that affect the tables & relationships i need to create. |
#5
|
|||
|
|||
3rd tier level table
I've had to adapt what you have said because although i can attach presenters
to individual sessions i can't do the same for the trainees as which sessions they will take is unlikely to be known in all circumstances and there isn't really a requirement to differenciate. In most circumstances the trainee will take all sessions, where they don't i have not got a requirement to record this information. If i were starting this from scratch i'd do it your way, unfortunately as i am now implementing a version 2 of an existing database i have to work within certain restraints (time being one) and that requires me to keep the registrations related to the course ID and everyone ID. So this is my compromise. Everyone ====== EveryoneID(pk) (other fields) Course ===== CourseID (other fields) Registrations ======== RegID EveryoneID CourseID (other fields) Presenter ====== PresenterID Everyone ID (other fields) Course/Presenter ====== Presenter ID Course ID Sessions ======= Sessions ID Course ID (other fields) Sessions/Presenter =========== Sessions ID Presenters ID Does this make sense. I was considering that i could have a registrations/sessions link table to implement the option of attaching trainees to courses without disturbing the course registrations relationship. It would be nice to have choice to implement this functionality later on even though i am constrained now. ps your website is great, simply designed but full of loads of useful information (databases) i miss those days when you didn't have to squint at the page to find what you wanted or get bamboseled by flash everywhere. thanks |
#6
|
|||
|
|||
3rd tier level table
Part of the problem with table design is that it depends a great deal on the
actual business rules. These can be different for different organizations. So you have to take any design suggested and compare that to YOUR model. That said, I think that while not optimal, your design could work. I would also suggest you create the Registration_Session linking table to create a M:M relationship between Registration and Sessions. One last thing, however, is you DON'T need the Course/Presenter table. Since you're assigning Presenters to Sessions and any individual Session can be for only one class, you can see which presenters are assigned to each courses that way. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Engels" wrote in message ... I've had to adapt what you have said because although i can attach presenters to individual sessions i can't do the same for the trainees as which sessions they will take is unlikely to be known in all circumstances and there isn't really a requirement to differenciate. In most circumstances the trainee will take all sessions, where they don't i have not got a requirement to record this information. If i were starting this from scratch i'd do it your way, unfortunately as i am now implementing a version 2 of an existing database i have to work within certain restraints (time being one) and that requires me to keep the registrations related to the course ID and everyone ID. So this is my compromise. Everyone ====== EveryoneID(pk) (other fields) Course ===== CourseID (other fields) Registrations ======== RegID EveryoneID CourseID (other fields) Presenter ====== PresenterID Everyone ID (other fields) Course/Presenter ====== Presenter ID Course ID Sessions ======= Sessions ID Course ID (other fields) Sessions/Presenter =========== Sessions ID Presenters ID Does this make sense. I was considering that i could have a registrations/sessions link table to implement the option of attaching trainees to courses without disturbing the course registrations relationship. It would be nice to have choice to implement this functionality later on even though i am constrained now. ps your website is great, simply designed but full of loads of useful information (databases) i miss those days when you didn't have to squint at the page to find what you wanted or get bamboseled by flash everywhere. thanks |
#7
|
|||
|
|||
3rd tier level table
That's great thank you Roger
I've been stuggling witht the idea of starting the Version 2 of this database from scratch which i would love to do but would probably mean that they would pull the plug on the project creating far too much hassle. "Roger Carlson" wrote: Part of the problem with table design is that it depends a great deal on the actual business rules. These can be different for different organizations. So you have to take any design suggested and compare that to YOUR model. That said, I think that while not optimal, your design could work. I would also suggest you create the Registration_Session linking table to create a M:M relationship between Registration and Sessions. One last thing, however, is you DON'T need the Course/Presenter table. Since you're assigning Presenters to Sessions and any individual Session can be for only one class, you can see which presenters are assigned to each courses that way. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Engels" wrote in message ... I've had to adapt what you have said because although i can attach presenters to individual sessions i can't do the same for the trainees as which sessions they will take is unlikely to be known in all circumstances and there isn't really a requirement to differenciate. In most circumstances the trainee will take all sessions, where they don't i have not got a requirement to record this information. If i were starting this from scratch i'd do it your way, unfortunately as i am now implementing a version 2 of an existing database i have to work within certain restraints (time being one) and that requires me to keep the registrations related to the course ID and everyone ID. So this is my compromise. Everyone ====== EveryoneID(pk) (other fields) Course ===== CourseID (other fields) Registrations ======== RegID EveryoneID CourseID (other fields) Presenter ====== PresenterID Everyone ID (other fields) Course/Presenter ====== Presenter ID Course ID Sessions ======= Sessions ID Course ID (other fields) Sessions/Presenter =========== Sessions ID Presenters ID Does this make sense. I was considering that i could have a registrations/sessions link table to implement the option of attaching trainees to courses without disturbing the course registrations relationship. It would be nice to have choice to implement this functionality later on even though i am constrained now. ps your website is great, simply designed but full of loads of useful information (databases) i miss those days when you didn't have to squint at the page to find what you wanted or get bamboseled by flash everywhere. thanks |
Thread Tools | |
Display Modes | |
|
|