Table Design - Variation on Districts/Residents/Classes/Employees

[TC]

I am a beginner at this (but am determined to break away from my one-table

duplication problems).

You've made a very good attempt, and your comments do not sound like a
beginner's!

I can't figure out what I'm doing wrong, though. Let me describe what

I'm doing from top to bottom:

I've taken the liberty of rearranging and rewording parts of your original
post, in order to put my comments in the right places.

There are "districts":
tblDISTRICTS
fldDistrictID (PK)
fldDistrictName
Fine.


Each district contains many "residents":
tblRESIDENTS
fldResidentID (PK)
fldResidentName
fldDistrictID (FK to tblDistricts)
fldClassID (FK to tblClasses)
Fine.


There are various "classes", each of which is sponsored by a single

district, and has an instructor & a principle.

tblCLASSES
fldClassID (PK)
fldClassName
fldDistrictID (FK to tblDistricts) <- sponsoring district.
fldInstructorID (FK to tblEmployees)
fldPrincipalID (FK to tblEmployees)
Fine.


The Instructors and Principals are both "employees":
tblEMPLOYEES
fldEmployeeID (PK)
fldEmployeeName
Fine.


All residents go to classes. This is complicated by the fact that the

residents can be assigned to classes that belong to a different district
from the one they live in. From what I have read, I think I need some type
of intermediate table(s) between the primary "tblDISTRICTS" table and the
"tblRESIDENTS" and "tblCLASSES". (snip rest).

If a resident can go to many classes, and a class can have many residents,
this a classic many-to-many relationship between those classes & residents.
You resolve this by creating a junction table with a so-called "composite"
(multi-field) primary key. The composite primary key comprises, the primary
keys of the tables wth the M:M relationship. So:

tblCLASS_RESIDENT
fldClassID ( composite )
fldResidentID ( primary key )
other (non-key) attributes for *that resident* in *that class* - for
example, date enrolled.

To add a resident to a class, just create the appropriate new record in that
new table. Note that it is irrelevent what district the resident lives in
(but this could, if necessary, be obtained by looking-up that resident in
the RESIDENTS table), and also, it is irrelevent what district sponsors that
class (but that could, if necessary, be obtained by looking-up that class in
the CLASSES table).

So you were just about 99% there!

HTH,
TC

 

[Marcia]

I am a beginner at this (but am determined to break away from my
one-table duplication problems). I can't figure out what I'm doing
wrong, though. Let me describe what I'm doing from top to bottom:

There are "districts":
tblDISTRICTS
fldDistrictID (PK)
fldDistrictName

Each district contains many "residents":
tblRESIDENTS
fldResidentID (PK)
fldResidentName
fldDistrictID (FK to tblDistricts)
fldClassID (FK to tblClasses)

Each district also sponsors various "classes," and all residents go to
the "classes" which are staffed by an Instructor and a Principal.
This is complicated by the fact that the residents can be assigned to
classes that belong to a different district from the one they live in:
tblCLASSES
fldClassID (PK)
fldClassName
fldDistrictID (FK to tblDistricts)
fldInstructorID (FK to tblEmployees)
fldPrincipalID (FK to tblEmployees)

The Instructors and Principals are both "employees":
tblEMPLOYEES
fldEmployeeID (PK)
fldEmployeeName

From what I have read, I think I need some type of intermediate
table(s) between the primary "tblDISTRICTS" table and the
"tblRESIDENTS" and "tblCLASSES." I tried creating two more tables
between them [tblDISTRICTRESIDENT (fldDistResID-PK, fldResidentID-FK)]
and [tblDISTRICTCLASSES (fldDistClassID-PK, fldClassID-FK)], but this
doesn't work either. I must produce a report that references the
residents that live in the district; but other reports show the
worksites that are sponsored by the district.

Likewise, I tried to fix the Instructor and Principal fields (since
they both come from the tblEMPLOYEES table) in a similar manner, but
that didn't work either.

I guess I just don't "get it" yet. Either I am incorrectly
configuring the intermediate junction tables, or I need to do
something else entirely. Any advice would be greatly appreciated.

Thanks,
Jessi

 

[TC]

Hi, glad it helped.

Before, it was: "Residents enroll in Classes". Hence the many-to-many for
residents<>classes, hence the junction table residents_classes.

Now, it is:

"One Class may have many Sessions." So there is a 1:M for class<>session.
Have a session table with PK ClassID + session DATE (for example).

Then: "Residents enroll in Sessions." So now the *explicit* many-to-many is
for residents<>sessions, not residents<>classes. Create a joining table
resident_session (or whatever) with PK = residentID + *session*ID. Now, a
resident is only enroled in a class, by virtue of being enrolled in a
*session* which belongs to that class.

HTH!
TC

 

[Marcia]

Thanks for your compliment, but you will see that I truly am a
beginner as I keep going with this...

Your solution worked PERFECTLY to solve the District>Resident>Class
problem! I realize now that my previous attempts at a junction table
failed because, although I had created the third table, I was still
putting the actual resident enrollment data into the wrong tblClasses
*table* - duh! Thank you!

My next problem with this whole thing sounds truly bizarre, and may
not have a solution: Each "class" is held twice a day, 5 days per
week -- for a total of 10 different sessions (Monday morning, Monday
afternoon, Tuesday morning, Tuesday afternoon, etc.) Although each
Resident is initially assigned to all 10 sessions of one primary
class, a few of the Resident's sessions may be later re-assigned to
another class (even a class sponsored by a different home). In other
words, a Resident is PRIMARILY assigned to Class "A" and attends Class
"A" for 8 sessions, but he may attend Class "J" for the remaining two
sessions.

Is my best solution to create another "Sessions" table with ten fields
(Mon_AM, Mon_PM, Tue_AM, Tue_PM, etc.) that is somehow joined with the
tblClasses table (similar to the way you showed me earlier)? If so,
would it look something like this?

tblSESSIONS:
fldSessionID (PK)
fldSessionName (for Mon_AM, Mon_PM, etc.)

tblSESSIONS_CLASSES:
fldSessionID (composite)
fldClassID (PK)

If this will work, how does it join back to the tblRESIDENTS_CLASSES
table, and which table do I put the actual Resident enrollment data
in?

OR..........

Should I just forget about this and do something else? Maybe I should
create ten drop-down fields for the individiual sessions in the
Residents' table that contain the class names. As the class names
change, however, that would mean modifying the drop-down options ten
different times... (Can you see that I'm reverting back to my
flat-file ways? Ha!)

Any help is appreciated! Thanks,

Jessi

 

[Marcia]

Thanks so much! I think that I understand it a little better now...
(the concept comes and goes. Ha!).

Jessi