Class, Sessions, Attendees, What is the relationship?

[Banana]

After long, long, long hiatus of doing other stuff, I wanted to get around to update the database. One thing I had set up wrong was the attendance rosters.

I just need to be able to create a new class, which may have more than one sessions, then make a list of registrants for a particular class, then be able to track their attendance per session for this class.

However, my Google-Fu is lacking as every templates I've looked at doesn't really answer my question, which how three entities relate to each other.

I know for sure that Class-Attendees is a one many relationship. Same attendee can register for more than one class, so that's no brainer. Likewise, Class-Session should be also be one many relationship for obvious reasons. However, if I relate Session to Attendee, it would create a circular relationship between the three entities.

I've toyed with the idea of having a hierarchial tables of Class -> Attendees -> Sessions, but that does not relate the sessions to the class, which is necessary.

I wonder if I should create a fourth table to contain attendances and use Session and Attendees as FK, but that still wouldn't fix the possiblity of a circular relationship?

Any insights will be greatly appreciated. TIA

 

[Banana]

Anybody has a suggestion? Thanks

 

[Len Boorman]

Not sure completely with this but
Attendee 1 to M Class

Session 1 to M Class

Is this actually correct or would a Class occur in many sessions ?

Think if you can post the individual relationships between the entities may be able to make some ground

L

 

[Banana]

Actually,

Attendee 1 to M Class

Class 1 to M Sessions

And if my logic is right, Attendee M to M Sessions, since many attendees can be at one sessions, and may be at other sessions as well. This is where I intend to track their attendance.

Don't have my relationship handy at this computer:

tblClass
ClassID
ClassTypeID (lookup table)
FundingSource
Blah blah

tblAttendee (actually is a junction table)
ClassID
PersonID (the tblPerson has all personal information)

Sessions... I haven't created a table yet because I'm stumped on how to make one without circular reference... but it definitely would have those attributes:

SessionID
SessionDate

 

[Len Boorman]

Okay

Attached is Powerpoint that is a suggestion

Basically Attendee, Class and Session are distinct Entities in their own right. Relationships between them are therefore decomposed tables

L

 

[Banana]

Brilliant! Makes totally sense.

Just to see if I have the theory right:

Both attendees and sessions does depend on class's existence; no class, no attenees and/or sessions. However, sessions can exist without any attendees and an attendee can register for a class and never attend any of sessions. Therefore there would be no direct relationship between sessions and attendees, but rather indirectly through the class.

Is that right?

 

[Banana]

Ok, now I'm confused.

I set up the tables as Len showed. Except I had no idea where I would store the information about whether a particular attendee was at a particular session. My first impulse was a query but that doesn't store information.

Creating a junction table between attendees and sessions would be circular, I think.

So, how would that be accomplished?


PS: If that's any help, I intend to have a datasheet on my form showing a crosstab of attendees and sessions with checkboxes to indicate whether they were present at a particular session. Somehow unsurprisingly, all templates I could find were Excel, not for Access.

 

[Len Boorman]

Okay

First the relationships explanation

1) A class may have many Attendees
2) An attendee may go to many Clases

Therefore it is M:M between Class and Attendee.

Similarly between Class and Session
1) A Class may appear in many Sessions
2) A Session may have many attendees

Again M:M

Now Attendee to Session is a Transient Dependancy. Therefore Attendee would not appeare directly in a Session. The relationship is via Class relation.

So it is possible to get a via of which Attendees were at what Sessions but it is via Class

So make sure that your Class/Session intermediate table is correctly and fully populated with required values

L

 

[Banana]

Okay, I see.

Here's the present setup:

tblPerson (this is the main table for my database)
PersonID (PK)
FirstName
LastName
Category
Gender
Ethnicity

tblClass
ClassID (PK)
ClassTypeID (LookUp)
FundingSource
ClassNotes

tblAttendees
AttendeeID (PK)
ClassID (FK)
PersonID (FK)

(Whether a person in tblPerson is an attendee or not is implied in this junction table, and as Len shows, is indeed a M:M relationship)

tblSession
SessionID (PK)
ClassID (FK)
SessionDate

(this is actually a 1:M relationship; see below.)


Len, you proposed that Class to Session is M:M:

Similarly between Class and Session
1) A Class may appear in many Sessions
2) A Session may have many attendees

Again M:M

which makes no sense; if we substituted attendees with class, that wouldn't work; as a session can belong only to one class and therefore is a 1:M. But I do agree that a session can have many attendees.

I'm just scratching my head over where the information about attendance per session should be stored (assuming it's supposed to be stored) and not seeing how relating via Class would help the matter.

Nonetheless, thanks so much for your help and patiene so far, Len.

PS: Where could I go and read up on the concept of Transient Dependency? My google seach wasn't exactly relevant to the database theory.

 

[Len Boorman]

tblPerson (this is the main table for my database)
PersonID (PK)
FirstName
LastName
Category
Gender
Ethnicity

tblClass
ClassID (PK)
ClassTypeID (LookUp)
FundingSource
ClassNotes

tblAttendees
AttendeeID (PK)
ClassID (FK)
PersonID (FK)
.

Surely a Person is an Attendee. Therefore the Attendee ID is irrelevant and Redundant

(Whether a person in tblPerson is an attendee or not is implied in this junction table, and as Len shows, is indeed a M:M relationship)

tblSession
SessionID (PK)
ClassID (FK)
SessionDate

(this is actually a 1:M relationship; see below.)


Len, you proposed that Class to Session is M:M:

which makes no sense; if we substituted attendees with class, that wouldn't work; as a session can belong only to one class and therefore is a 1:M. But I do agree that a session can have many attendees.

I am confused by your use of a session, So looking at your table design

SessionID (PK) ClassID (FK) Session Date
1............................1..........................01/01/01

Now by definition since SessionID is PK you cannot have another instance of another class in that same session ID.

So what is a Session ?. Your table definition says it is a Class on a Date so why define a Session. Is not Class and Date Enough

Dependency's are part of the Normalisation process. Transient dependancy's are associated with 3rd Normal Form. So normalisation is the process and it is totally fundamental to relational databases, That is Access, Oracle, SQL Server and any other relational database you can think of

keep smiling

L

 

[Banana]

keep smiling

L

Certainly will! Thanks for the thoughtful reply.

Surely a Person is an Attendee. Therefore the Attendee ID is irrelevant and Redundant

Sure, if the database serves only to record attendances but it has other uses beyond that, so it is necessary to decompose the information about a person from an attendee (e.g. not all persons in my database are attendees, but attendees and non attendees share some attributes such as ethnicity, gender, etc which is why tbl Person exist)

I am confused by your use of a session, So looking at your table design

SessionID (PK) ClassID (FK) Session Date
1............................1..........................01/01/01

Now by definition since SessionID is PK you cannot have another instance of another class in that same session ID.

So what is a Session ?. Your table definition says it is a Class on a Date so why define a Session. Is not Class and Date Enough

Maybe a real example will help: We usually have classes that are a set of three sessions or ten sessions. We would meet once a week for ten times, meaning that our class had ten sessions. We have various classes that covers different materials that are offered on a regular basis, so I need a mean of being able to look a particular type of class and analyze the data for this type or perhaps do a comparsion between two different class types; that's why there's a lookup table (ClassTypeID). I would like to be able to record attendance per session, which shouldn't belong in tblClass.

Logically, a session can belong only to one class, but a class can have a many sessions or even just one. Attendance does not belong to class because this is an attribute of a session. However, I can't directly relate attendees to sessions. Also, because a class may have several sessions which mean a group of people may meet more than once, therefore, date of meeting should be an attribute of session rather than class, no?

Dependency's are part of the Normalisation process. Transient dependancy's are associated with 3rd Normal Form. So normalisation is the process and it is totally fundamental to relational databases, That is Access, Oracle, SQL Server and any other relational database you can think of

How odd. I don't recall seeing this specific term "transient dependency" when I read up on normalization back then. Nonetheless, I'll go and see if I can find some normalization article that expand on that concept.


I hope that clears thing up.

 

[Len Boorman]

Persons and Attendees are basically the same

Differentiate whethrt Attendee or Not with Flag on Person Table if you need to. Consider "a Person May or May not attend a class". Could a Non Attending person change sides and become an attendee ?

Got to be quick on this one at the moment.

Class and session. Believe from your explanation that these roles are reversed in ERD

A Class consists of Sessions

Therefore an attendee attends a Session not a Class
A Class is composed of Sessions

L

 

[Banana]

Len, looks like you and I are agreed on the definition of attendees, sessions and classes.

You are right that an attendee attends a session. Therefore, the table structure should be something like this:

Class 1:M Sessions 1:M Attendees

However, this doesn't really help when I want to have a list of who has registered for a class. There is a possibility that an attendee has registered for a class but never attended to any of the sessions. The above structure wouldn't show this at all.

This is the crux of my confusion. If we talk about registration, attendees belong to classes. But if we talk about attendances, attendees then belongs to sessions. I need to record both registration and attendances without creating a circular relationship.

Did that help?

 

[Len Boorman]

when I want to have a list of who has registered for a class.

Aha we have not actually mentioned Registration before.

Changes the flavour because Class now has an additional attribute of Registered (being a Person) but that screws up Class

You have therefore a new Relation called Registration

Takes PK's from Class and Person as FK's to provide a two field PK. Has additional attributes of Date registered and Fee if applicable. That sort of thing.

Rather that deciding what the relationships are as an initial step define the attributes of each entity (table) first.

Get the Entity Diagram sorted first I use Powerpoint, Define relationships,PK's, FK,s and data types before you head off into Access

L

 

[Banana]

All right, I will sketch up a diagram to show a bit later.

A note: tblAttendee, for me at least, has served the purpose of keeping "registration". Right now, there's no attributes such as fee paids, but if it's going to be included, this is the table where it will go.

Thanks again!

 

[Len Boorman]

All right, I will sketch up a diagram to show a bit later.

A note: tblAttendee, for me at least, has served the purpose of keeping "registration". Right now, there's no attributes such as fee paids, but if it's going to be included, this is the table where it will go.

Thanks again!

Maybe but what will you do if somebody wants to register for two classes ?.

Murphy's Law says that will happen.

Better to bite the bullet now and cover your bases otherwise it will be a complete rebuild later.

Again Registration is an attribute that does not have full dependancy on the PK. Violates Normalisation Rules

It may sound a bit of a chant but believe me Normalisation is the basis of most peoples problems if it is not done, and also it is one on the more difficult aspects of the design process... so either way it can hurt.

No pain...No gain

L

 

[Len Boorman]

Be going home soon but wanted to really emphasise the importance of this design process. By having preconceived ideas you ended up with circular references.

You must do your best to identify the Entities and thi is sometimes not easy.

Sometimes it may seem convenient to put something in a table but that is a short walk to a headache.

You do not get it right first time every time believe me

But you must not be distracted in trying to get it right.

I have started of databases thinking 6 tables and then thought more and suddenly there are 12, but later the benefits of a solid design really came good.

Try not to loose too much sleep

I find walking the dog at about midnight brings solutions
Used to not now though (no dog)

L

 

[Banana]

Len,

Thanks so much for the pointers to the date.

After getting other things out of way, I got back to the database and thought out what I needed and made a relationship diagram. Now, I don't think this is right, but I do know that it has all pertinent information that I need so it's more of juggling the properties to right entities and drawing the right lines so there'll be no circular reference.

I also removed other tables from the view to keep it uncluttered. All hidden tables relate only to the tblPerson, and has nothing to do with registration/attendance and serves a different function. Anyway, here it is.

Len says that registration fee shouldn't be in tblAttendee, and that it doesn't have a full dependency on the PK then cite what if I have a attendee registering for two class. I'm not seeing anything wrong with that because same attendees can actually do that in that table already? Just wanted to make sure I wasn't missing something obvious on my nose.

One thing need mentioning: I removed the ClassDate from the tblClass, since I won't need it if I'm going to have SessionDate, even for a one session class.

TIA.

 

[Len Boorman]

Had a look
Some initial thoughts

Attendee table is actually registration I believe, It is decomposed from a M:M from Person and Class. PK should therefore be the two fuields from the associated tables PersonID and ClassID. These two fields become the PK and also are FK's
Attendance is again a decomposed table between Person and Session, PK created in same manner
Class held in a County and the relationhips indicate a County could have many Offices. Therefore you onlyknow the County for the Class not the Office. Maybe a Class should be at an Office and then you can establish the County

Why have separate table for Gender. there are only two I believe and this can be controlled directly at the Person Table.

You should be aware that in tables where you have only a PK and a single attribute you will have to set unique index's to avoid potential duplication of Ethnicity for example

L

 

[Banana]

So I take it that the tblAttendance isn't circular then? The relationship are defined correctly?

Yes, you are right that tblAttendees is really registration. I'm just too afraid to re-name tables after a particularly bad episode in past with updating the new name. There were three steps: change name in VBA which isn't bad as I can use Find & Replace, change name in Access forms (it's a subform, which I can't find & Replace, so have to step gingerly here), and of course AutoCorrect won't help anything here, which I had turn when I created the database. At least I can identify it as keeping registration in its properties field.

As for offices/counties, actually a office may cover more than one county, and for our reporting, we need to indicate which county we held our workshops, that's all. Because offices are given a specific boundary, we can tell which office taught a class based on which county it was.

Gender table has one more entry: Unspecified, which Yes/No field wouldn't be sufficient and from design perspective, using triple state (Yes/No/Null) may be confusing to my users, so I decided to outsource the three options to a table: Male, Female, Unspecified.

Very interesting tidbit about setting a unique index for a lookup table with single attribute! I didn't know that. I'm not sure if I understand how a duplication can be created with same person?

Same for using FKs as a PK. I was aware that you can create a composite keys, but some seems to favor using a separate PK and at that time, seemed prudent to me since what if say ClassID get changed for some reason, which would require changing the ClassID in tblAttendee, and if it's a PK, it'd violate the rule of PK that it must be durable; never changes?