Class, Sessions, Attendees, What is the relationship?

[Len Boorman]

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

Sounds right but jury still out a bit still. need to mull it over. Only got a majority in favour at the moment

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.

Can live with that although it does reinforce the importance of the initial design phase

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.

Just shows how difficult it is to comment/advise on another persons project

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.

Unspecified eh. Unusual but we live and learn. Yes triple state would confuse the hell out of a user

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?

Example
PersonID........1 This is the PK
Name.............Fred

lots or records here

Next record
PersonID......598763
Name...........Fred Actually same Fred as before. New User did not know he had been here before

Shows that PK's need to be chosen carefully and potentially the need for a Non Meaningful PK but a meaningful unique index

There may indeed be more than one Fred so Name combined with First address line is probably okay. Unlikely 2 Freds at same address, Could have used DoB as alternative maybe again with Fred. You need to weigh up what you are going to go for.

With something like Category where you are only going to have a description then why store 2 fields both actually with unique Index's (all PK's have Unique Index) when one will do the job admirably.

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?

There are various schools of thought on PK selection. In my opinion Meaningful PK where I prefer. Purely my opinion and preference as basically I am in a "Identification by Number" type enviroinment. I can also appreciate the other school of thought and do not say it is wrong at all.

PK Durable and never changes. Absolutely but believe this says that the attribute you have chosen as PK never changes. There is no problem with the value changing is there. That is very much to the fore where you inly need to change a single value for the Cascade Update to kick in. Example. I have a project called LJB1 which later changes to JLB1. PK has not changed, only its value


L

 

[Banana]

Sounds right but jury still out a bit still. need to mull it over. Only got a majority in favour at the moment

Quite understandable. This is same dilemma I'm in right now. One side of me reasons that since both Attendees and Sessions depend totally on existence of a Class, and they are independent of each other, tblAttendance would be only able to take in existing records from each table, so the relationship is not circular. Yet, my gut stills churns a bit when I look at the relationship diagram. This may be because I'm so used to the idea that tables is best defined in a hierarchical fashion and seeing how experts use star or snowflake schema to base their database, this runs contrary to what I've seen.

Can live with that although it does reinforce the importance of the initial design phase

Undoutably!

Just shows how difficult it is to comment/advise on another persons project

Still, I'd rather that you had pointed it out and I explain thus than to overlook a potentially serious flaw. I am human and I certainly will make mistake, even if I have the training and experience.

Example
PersonID........1 This is the PK
Name.............Fred

lots or records here

Next record
PersonID......598763
Name...........Fred Actually same Fred as before. New User did not know he had been here before

Shows that PK's need to be chosen carefully and potentially the need for a Non Meaningful PK but a meaningful unique index

There may indeed be more than one Fred so Name combined with First address line is probably okay. Unlikely 2 Freds at same address, Could have used DoB as alternative maybe again with Fred. You need to weigh up what you are going to go for.

Let's see if I'm understanding it:

By setting the FKs in tblPerson to unqiue indexes, I would be preventing a duplicate of Fred who is a caucasian male, but not lockup of the database if a user tries to enter a Bob who is also a caucasian male?

With something like Category where you are only going to have a description then why store 2 fields both actually with unique Index's (all PK's have Unique Index) when one will do the job admirably.

I used to have no autonumbers in my lookup tables in my old design prior to the overhaul when I wised up and was told that it'd create some problems. One problem would be that it'd render queries accessing such tables nonupdatable, among others. Or am I misunderstanding you?

...snip...

L

Very interesting! I'll have a look into those schools. For now, since I've already established this database with autonumbering PK, it's best to leave sleeping dogs alone. I don't want anybody who inherit the database to damn my soul for eternity for some experimentation.

 

[The_Doc_Man]

OK, here is a thought

Person table. Tells you about a person. Nothing to do (directly) with any class.

Class table. Tells you about a class. Includes topic description, title of class, etc. etc. See note below regarding what this REALLY describes.

If this were a semester and the class was only offered once per semester, then this is as far as it goes. If it is a semester situation and the class is offered twice (say, 10-11 AM MWF or 2PM-3:30 TTh), this description is not complete. So before we go too far, I'm going to introduce the concept and show you how you have to handle it.

Call the individual offerings sessions. A class with two distinct sessions (in THIS definition) has a 1:M relationship to sessions. The session table includes time, place, and date-range of offering.

NOTE that if you only ever offer one session per semester using this definition, you can collapse it back to one layer of tables where class and session data (my definition of sessions) are in the same table.

The individual times at which the class meets are, for lack of a better term, Meetings. These have dates within the range shown for the sessions. They are such that Session:Meeting is 1:M - and there is no easy way to populate this ahead of time except either direct (manual) date entry or run a bit of VBA code to pre-define the dates you could use.

OK, using my definitions, you have three more tables.

Registration - M:M junction table with person and session ID. Because the person registers for a specific session (if it is offered more than once).

MtgSched - 1:M table showing the dates on which each meeting of the class or session will occur.

Attendance - M:M junction table with person and MEETING Id. Because the person attends meetings of the class or session. THIS table contains the dates of the meetings, one per entry.

Now, to get a class roll, you have a report on a JOIN of Class to Session to Registration to Person, with section breaks at Class and Session. The registration table merely joins Person to Session. You get the person's name and other details through the join.

Now, to get an ATTENDANCE roll, you have a report on a JOIN of session to meeting to attendance to person, with section breaks at session and meeting. If you want to know who was there, it is in the attendance table.

If you want to know who SKIPPED on day X, you need to find those meeting entries grouped by session & sorted by date, then do a "Not In" type of query (this is a sub-query). You want to find, for each date X, how many people in the registration list for the session were not at a meeting of that session on a date in the MtgSched table.

 

[Banana]

The_Doc_Man:

Thanks so much for chiming in.

1) We don't do any semesters or quarters here. We are not an educational institution so to speak, so our classes are more or less arbitarily offered on whenever date my office decide on.

2) Your definition of "Meetings" is my definition of Sessions. There are no single class that may be offered to two separate groups of people at different time (your definition of Session), though that is very very good thought; what if in future my office decide to offer two same class to two separate group of people. What I was doing is that if I had two class covering same materials at different times, I'd call this two separate Class. That's why I have ClassTypeID in the tblClass. What do you think of that?

3) Your explanation of how to report attendances and registration (your class roll) sounds very sensible and straightforward. I'll definitely be doing something among that line.

Thanks so much! It's so refreshing to have other people look at it and help expand my horizon.

 

[Len Boorman]

Non Updateable queries is something that gives people problems.

Basically is you extract a set of data using a query that uses more than one table, i.e the query has a Join then the data extracted cannot be updated.

So I believe that say you extracted Gendery and People data then you would not be able to update Gender But if you merely needed to update a persons gender then you do not need anything fromthe Gender table as such. You merely update a persons data to another value from the gender table. Combo box for example. If you had misspelt gender then you only need data from the gender table and cascde update would effect the change.

So you do not necessarily end up with unupdateable queries.

The other point re Index's

Yes the idea is to prevent a duplication. Sometimes it is not possible to have a PK as a single attribute. Take teh example of a company with many branches. maybe from a corporate branding the Branches all have the same name. So Branch Name is no good as a PK. In this circumstance you may have an Autonumber as the PK. Branch Name and say first address line become a Unique index. This prevents duplicating a Branch. If you do not have this and there is a duplication then the associated record FK's are shared between two (or more) PK values . So a search and extract of data is likely to get only some of the data.

As I said there are two schools on PK's. Both can be used, They can be mixed in a single application. There are pro's and con's each way.

Whenever I am asked to look at a db I lokk first for arelationship diagram. No diagram then db is very suspect. Next look at PK's. Autonumbers okay providing there are unique index's in critical areas.. No index's and I usually run a quick query looking for duplicated critical data. Surprises the hell out of people when you get a bunch of duplicated returned.

people say there is not much data so it can be eyeballed. Yeah right. Murphy's law rules.

I do find ituseful to actually write out statements of relationships. This does make you examine carefully what you are saying.

since both Attendees and Sessions depend totally on existence of a Class

Can there be a Class with No attendees. Okay nobody has registered yet and registrations are expected, but..........

Just an example of questioning

Design is the difficult bit

L

 

[Banana]

Len,

Thanks for some elaboration.

As for indexes, after thinking about it, it won't work for tblPerson, because I remembered that in one of database's other function, we use it to track demographics of people and we may provide service across telephone or e-mails in which there is no way to identify demographic or if someone does in fact comes into our office but does not choose to disclose their ethnicity, or may disclose ethnicity but not their names. Therefore tblPerson may have several entries where there are no FirstName/LastName but has information on demographics which may be duplicated (e.g. two caucasian males comes to our office on a day and disclose their ethnicity but not their names), which adheres to our contractor's requirements for reporting. They do accept the problem that it is possible to have the two entries be actually same person and are okay with reporting them as "incidents" rather than "individuals".

Of course this will not apply if I'm talking about our attendees or our clients, which is why I would enforce valdiation rules on the forms, rather within the tables. More work on my part, but allows support for overlapping and conflicting rules for different services we provide for tables that are shared among different services.

Re: Class with attendees. I thought about that, myself. What I'm seeing is that I can create class without either attendees and sessions. The immediate next step would to be add a session, as even class that meet only once would have still have single record in session table. Obivously, I'd want to make sure that "a minimum of one session" rule is enforced at all times, which can probably be accomplished from the forms or with a bit of VBA. So, if a single session is always created alongside with creation of a new class, it is now a trivial matter to add attendees as everything is now in the places.

I only hope that my reasoning isn't flawed.

 

[Len Boorman]

Len,

I only hope that my reasoning isn't flawed.

Thats what we all hope for. You just go around in circles until you are certain you have it sussed. And sometimes you have still got it wrong...

 

[Banana]

*bump*

All righty.

I believe I'm having some problems with the prototype database, and I'm thinking it may be due to normalization problems, therefore, I want to run the relationship diagram to make sure I have it all right.

Notes:

A class can have several sessions.
Attendee may enroll in more than one class.
tblSessionAttendance record only if an Attendee was present for a given Session. ClassID is implied in case if there are two sessions on same day.

Any feedback will be appreciated.

 

[Len Boorman]

At the moment I think the only comment would be that the field Attended in tblSessionAttend is not required. The fact that the attendee ID is present says that they attended. The difference between Class Registrations and SessionAttended would should those who did not attend although yoy would need to be sensitive to the SessionID

Work out what you believe to be the most difficult data set you will need to extract and build the queries to extract this data using some dummy data. If you get success in this then the remainder will probably be okay.

L

 

[Banana]

Len,

While you're right that field "Attended" is not *strictly* needed, I decided to implement the extra column because when I consider the scenario where an user may make a mistake in entering attendance records and need to modify; do I want to allow the user to delete records or simply clear a checkbox. By using a checkbox, the table is now pretty much "Add-and-update-only" table which means no maintenance is required for this table.

Right now, my test runs has had mixed results and that was with a relatively simple data set, and I found myself hassling over queries, which is why I suspected something may not be normalized correctly. Hence the post.

 

[wazz]

hi all. my 2 cents: in tblSessionAttendance i think you should be using ClassRegistrationID and not AttendeeID so that only people who have actually registered for the class may appear in attendance. (if i missed something in a previous post - i.e. if this is actually possible - ignore this post). if you don't do this your queries might give you trouble, i.e. you won't know why an attendee hasn't registered for the class. it will also be easier to figure out which class you are dealing with. i also think the Attended field is not necessary.

as mentioned or hinted in a couple of previous posts, there might not be an easy way to update some of these tables. people will have to be entered in the 'registered' table and the 'attendance' table separately because they are "different". it's a pain but i don't think there is a good way around it. i've been working on something similar having to do with performances and it seems sometimes that there is *nothing* about a performance that could not change from one performance to the next - people get sick, repertoire can change, etc. it's a headache. but there is something to go on: the Date/Time. i think this is very important for you too (hopefully i didn't miss something). the time is absolutely necessary if the "same" class can happen twice in one day (which isn't really the same class (Session), is it?!?). i would design for this possibility. (in tblSession change the SessionDate field to SessionDateTime and include the time (or add time fields?)). unfortunately it means a lot of stuff might have to be reentered for every date/time, but there are ways to automate data entry.

i think it looks like you pretty much have it. (go with ClassRegistrationID in tblSessionAttendance ).

 

[Banana]

Wazz, thanks so much for the suggestion.

The idea of using ClassRegistrationID instead of AttendeeID was actually what I did in a earlier prototype but ran into problems. I'm afraid I can't remember what it was, but I think Access was having trouble maintaining data integrity (complaining about missing or unmatched key, I think).

Thanks for the suggestion about time. I'll consider this as soon as I can get the prototype working as expected. One problem at a time.

Any more suggestions?

 

[The_Doc_Man]

I believe we have discussed at least a facet of this puppy in the Design issues sub-theme. Did that rather extended excursion to NULL-ville help you cement your thoughts on this problem, Banana?

 

[Banana]

Yes, a lot.

However, if you look at the last page, I am suspecting I am having a problem with the present structure as everything isn't working as well as I'm expecting it to be, so wanted to step back and make sure everything is properly normalized.

Maybe you have any thought on the diagram on last page?

 

[Banana]

Anybody has an suggestion?