Table Design & Relationships

[GDW]

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

 

[Jeff Boyce]

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

 

[GDW]

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]

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.

 

[GDW]

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

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>

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


message