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
|
|||
|
|||
Table Design & Relationships
At this point my brain is, well, in turmoil. I hope I can get it back into
some kind of working order. I have students who own dogs (1:M) ... so far so good Now, my confusion begins.... I have 4 ClassSessions each year ( the quantity of sessions could change, but not likely) In each ClassSession I have several Classes, made up of DayofWeek, TimeOfClass, and SkillLevel (Some examples; July2004 ClassSession consisting of Monday, 7:00PM, Beginner, Monday, 8:00PM Intermediate, Tuesday, 7:00PM Beginner, ; etc.. A ClassSession of September2004 might have the same set of Classes, but may differ slightly.) I think I need a M:M (Registration junction table) from Dog to Class(orSession) .... this is where my confusion really begins. My brain says I need another M:M (Session_Classes junction table) .. but I'm not sure and ... I'm not sure how to join the Class and/or Session to the Registration. My goal is to Register a dog in any Class from a current ClassSession but maintain any History of past ClassSession/Class that a Dog has registered for. (I think a simple archive of past Dog/Session/Class is what I need to maintain any history.) I'm not to concerned about the History part at this moment. Can someone help me get me headed in the right direction? Gary |
#2
|
|||
|
|||
Gary
If I'm understanding your situation correctly... You have: Class Session (or "Terms", or "Cycles", or ...) Classes, categorized by DayOfWeek, TimeOfDay, SkillLevel Students (don't you mean "Owner"?) Dog (I assume you include dog's name, breed, DOB, or other identifying characteristics, as I can believe there are a lot of "Rover"s out there) You have: One Owner with (potentially) many Dogs (?!and every one of them named "Rover"?!) One Class Session with (potentially) many Classes One "Registration" for every valid combination of Owner/Dog/ClassSession/Class I suspect it is this latter relationship that is troublesome. This is one approach: tblOwner OwnerID OwnerFirstName (e.g., Gary) OwnerLastName ... OwnerPhoneNumber tblDog DogID OwnerID (foreign key, from tblOwner) DogName DogDOB ... (p.s., if you every have the same dog "owned" by a different owner, or multiple simultaneous owners, you'll need to remove OwnerID from this table and create a junction table between Owner and Dog) tblClassSession SessionID SessionDescription tblClass ClassID ClassSkillLevel trelClassSessionClassOffering SessionClassID SessionID ClassID DayOfWeek TimeOfDay Fee Max Number of Registrants trelRegistrations RegistrationID SessionClassID (which ClassSessionClassOffering) DogID (or, if M:M for Owner/Dog, the OwnerDogID) DateRegistered AmtPaid This is just one person's opinion... -- Good luck Jeff Boyce Access MVP "GDW" gdw@tds wrote in message ... At this point my brain is, well, in turmoil. I hope I can get it back into some kind of working order. I have students who own dogs (1:M) ... so far so good Now, my confusion begins.... I have 4 ClassSessions each year ( the quantity of sessions could change, but not likely) In each ClassSession I have several Classes, made up of DayofWeek, TimeOfClass, and SkillLevel (Some examples; July2004 ClassSession consisting of Monday, 7:00PM, Beginner, Monday, 8:00PM Intermediate, Tuesday, 7:00PM Beginner, ; etc.. A ClassSession of September2004 might have the same set of Classes, but may differ slightly.) I think I need a M:M (Registration junction table) from Dog to Class(orSession) ... this is where my confusion really begins. My brain says I need another M:M (Session_Classes junction table) .. but I'm not sure and ... I'm not sure how to join the Class and/or Session to the Registration. My goal is to Register a dog in any Class from a current ClassSession but maintain any History of past ClassSession/Class that a Dog has registered for. (I think a simple archive of past Dog/Session/Class is what I need to maintain any history.) I'm not to concerned about the History part at this moment. Can someone help me get me headed in the right direction? Gary |
#3
|
|||
|
|||
Hi Jeff,
Thanks for the quick response and help. It looks like what I had already was close to your suggestions. I've implemented your suggestions and so far so good. My problem now is that it looks like trelClassSessionClass will bloat with the same Day/Time/Level. If I have 4 sessions/year I will have 4 classes with the same name. So if I have 20 individual Day/Time/Level x 4 Session/year that table will already have 80 records (x 5 years = 400 records, etc. Since this is a relativly small DB it probably won't be a big problem but I'd like to learn how to do it right. My next question is how do I keep from registering the same dog in the same Session/Class. Gary "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... Gary If I'm understanding your situation correctly... You have: Class Session (or "Terms", or "Cycles", or ...) Classes, categorized by DayOfWeek, TimeOfDay, SkillLevel Students (don't you mean "Owner"?) Dog (I assume you include dog's name, breed, DOB, or other identifying characteristics, as I can believe there are a lot of "Rover"s out there) You have: One Owner with (potentially) many Dogs (?!and every one of them named "Rover"?!) One Class Session with (potentially) many Classes One "Registration" for every valid combination of Owner/Dog/ClassSession/Class I suspect it is this latter relationship that is troublesome. This is one approach: tblOwner OwnerID OwnerFirstName (e.g., Gary) OwnerLastName ... OwnerPhoneNumber tblDog DogID OwnerID (foreign key, from tblOwner) DogName DogDOB ... (p.s., if you every have the same dog "owned" by a different owner, or multiple simultaneous owners, you'll need to remove OwnerID from this table and create a junction table between Owner and Dog) tblClassSession SessionID SessionDescription tblClass ClassID ClassSkillLevel trelClassSessionClassOffering SessionClassID SessionID ClassID DayOfWeek TimeOfDay Fee Max Number of Registrants trelRegistrations RegistrationID SessionClassID (which ClassSessionClassOffering) DogID (or, if M:M for Owner/Dog, the OwnerDogID) DateRegistered AmtPaid This is just one person's opinion... -- |
#4
|
|||
|
|||
Gary
I'm not sure I'd call it "bloat" if you had 4 (or 40) classes a year ... consider a university that offers the same class each term, perhaps multiple "copies" per term, year in, year out. The reason you use a separate row for each ClassSessionClass is to provide your Dogs with a specific row to connect to in Registration. That's why I included a ClassSessionClassID. And if you offer a Beginners class each year, 4 times a year, each starting at 8 pm on Tuesday, wouldn't you still want/need to know that "Bowser" was registered for the Beginners class that started on 1/4/2005? (If I left out fields for startdate, you need 'em!) By the way, you also need to record day, time and level because this could change from year to year. Who knows, maybe your business will grow enough to require that you add a field for [Facility], to show "where" the Beginning class on Tuesday at 8 will be held. If your Registration table records ClassSessionClass and Dog IDs, a simple approach to preventing the same Dog from Registering twice would be to set a unique index on that pair of fields. -- Good luck Jeff Boyce Access MVP "GDW" gdw@tds wrote in message ... Hi Jeff, Thanks for the quick response and help. It looks like what I had already was close to your suggestions. I've implemented your suggestions and so far so good. My problem now is that it looks like trelClassSessionClass will bloat with the same Day/Time/Level. If I have 4 sessions/year I will have 4 classes with the same name. So if I have 20 individual Day/Time/Level x 4 Session/year that table will already have 80 records (x 5 years = 400 records, etc. Since this is a relativly small DB it probably won't be a big problem but I'd like to learn how to do it right. My next question is how do I keep from registering the same dog in the same Session/Class. Gary "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... Gary If I'm understanding your situation correctly... You have: Class Session (or "Terms", or "Cycles", or ...) Classes, categorized by DayOfWeek, TimeOfDay, SkillLevel Students (don't you mean "Owner"?) Dog (I assume you include dog's name, breed, DOB, or other identifying characteristics, as I can believe there are a lot of "Rover"s out there) You have: One Owner with (potentially) many Dogs (?!and every one of them named "Rover"?!) One Class Session with (potentially) many Classes One "Registration" for every valid combination of Owner/Dog/ClassSession/Class I suspect it is this latter relationship that is troublesome. This is one approach: tblOwner OwnerID OwnerFirstName (e.g., Gary) OwnerLastName ... OwnerPhoneNumber tblDog DogID OwnerID (foreign key, from tblOwner) DogName DogDOB ... (p.s., if you every have the same dog "owned" by a different owner, or multiple simultaneous owners, you'll need to remove OwnerID from this table and create a junction table between Owner and Dog) tblClassSession SessionID SessionDescription tblClass ClassID ClassSkillLevel trelClassSessionClassOffering SessionClassID SessionID ClassID DayOfWeek TimeOfDay Fee Max Number of Registrants trelRegistrations RegistrationID SessionClassID (which ClassSessionClassOffering) DogID (or, if M:M for Owner/Dog, the OwnerDogID) DateRegistered AmtPaid This is just one person's opinion... -- |
#5
|
|||
|
|||
Jeff,
Got it! Thanks. I was going on the premise that a 'good' DB didn't duplicate any records. I knew that a flat DB could be done but it was inefficient and I took it as a cardinal 'rule' and doing my best not to break it I really appreciate your time and patience with this. Table design and relationships so far my weakest area and I know these are the foundation of a good DB. Your assistance helped understand it better. Thanks ... again. Now to get all the fields and forms that I need in place. One more question, for now, and maybe I should target it towards the forms NG. Could I create a paired listbox form with all the standard Classes in to move them to create a new ClassSession (i.e. January2004 -description) instead of having to create that session one class at a time? Gary "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... Gary I'm not sure I'd call it "bloat" if you had 4 (or 40) classes a year ... consider a university that offers the same class each term, perhaps multiple "copies" per term, year in, year out. The reason you use a separate row for each ClassSessionClass is to provide your Dogs with a specific row to connect to in Registration. That's why I included a ClassSessionClassID. And if you offer a Beginners class each year, 4 times a year, each starting at 8 pm on Tuesday, wouldn't you still want/need to know that "Bowser" was registered for the Beginners class that started on 1/4/2005? (If I left out fields for startdate, you need 'em!) By the way, you also need to record day, time and level because this could change from year to year. Who knows, maybe your business will grow enough to require that you add a field for [Facility], to show "where" the Beginning class on Tuesday at 8 will be held. If your Registration table records ClassSessionClass and Dog IDs, a simple approach to preventing the same Dog from Registering twice would be to set a unique index on that pair of fields. -- Good luck Jeff Boyce Access MVP "GDW" gdw@tds wrote in message ... Hi Jeff, Thanks for the quick response and help. It looks like what I had already was close to your suggestions. I've implemented your suggestions and so far so good. My problem now is that it looks like trelClassSessionClass will bloat with the same Day/Time/Level. If I have 4 sessions/year I will have 4 classes with the same name. So if I have 20 individual Day/Time/Level x 4 Session/year that table will already have 80 records (x 5 years = 400 records, etc. Since this is a relativly small DB it probably won't be a big problem but I'd like to learn how to do it right. My next question is how do I keep from registering the same dog in the same Session/Class. Gary "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... Gary If I'm understanding your situation correctly... You have: Class Session (or "Terms", or "Cycles", or ...) Classes, categorized by DayOfWeek, TimeOfDay, SkillLevel Students (don't you mean "Owner"?) Dog (I assume you include dog's name, breed, DOB, or other identifying characteristics, as I can believe there are a lot of "Rover"s out there) You have: One Owner with (potentially) many Dogs (?!and every one of them named "Rover"?!) One Class Session with (potentially) many Classes One "Registration" for every valid combination of Owner/Dog/ClassSession/Class I suspect it is this latter relationship that is troublesome. This is one approach: tblOwner OwnerID OwnerFirstName (e.g., Gary) OwnerLastName ... OwnerPhoneNumber tblDog DogID OwnerID (foreign key, from tblOwner) DogName DogDOB ... (p.s., if you every have the same dog "owned" by a different owner, or multiple simultaneous owners, you'll need to remove OwnerID from this table and create a junction table between Owner and Dog) tblClassSession SessionID SessionDescription tblClass ClassID ClassSkillLevel trelClassSessionClassOffering SessionClassID SessionID ClassID DayOfWeek TimeOfDay Fee Max Number of Registrants trelRegistrations RegistrationID SessionClassID (which ClassSessionClassOffering) DogID (or, if M:M for Owner/Dog, the OwnerDogID) DateRegistered AmtPaid This is just one person's opinion... -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Get data from combo box to popluate the next box | Lin Light | Using Forms | 4 | December 30th, 2004 05:01 PM |
Update - If statement | Dan @BCBS | Running & Setting Up Queries | 13 | December 14th, 2004 06:02 PM |
Access 97 - Table Relationships | Working girl | General Discussion | 8 | December 6th, 2004 05:49 PM |
Semicolon delimited text query help | Al Guerra | Running & Setting Up Queries | 3 | August 12th, 2004 11:50 AM |
Help with table design and relationships | Richard Wright | Database Design | 3 | June 20th, 2004 03:49 PM |