Table Stucture

[cuttsy]

Not Quite Normalized Enough

I am designing a database (just on paper at the moment). My knowledge of databases and access is of an okay standard but nothing more so feel free to point out my stupidity.

I have a problem with my first draft. Part of the database concerns itself with school pupils and mentors that at some point spend time teaching the pupils. The database needs to keep a record of the amount of time each mentor spends teaching and how much time each pupil has spent being taught.

Every time a Mentor teaches one or more pupils they fill out a form with their name, date, the details of the pupils that they taught and how long they spent with each pupil.

I originally had 3 tables:

Pupils(PupilID, Fistname, Surname, DOB)
Mentors(MentorID, Firstname, Surname)
Contact(MentorID, PupilID, TimeSpent, ContactDate)

I found what I think is a problem in that If I have MentorA and MentorB who together teach Pupil1 for 2 hours on the 25th November 2006 my Contacts table would look like this.

A, 1, 120, 2006-11-25
B, 1, 120, 2006-11-25

This does fine for recording how long each Mentor has worked:

MentorA - 120 min
MentorB - 120 min

However I have a duplication of - Pupil1, 120, 2006-11-25

When looking to see how long Pupil1 has spent being taught the data tells me 240 min. This is not correct. It is vital that the database can tell exactly how long each pupil is involved with a Mentor.

Does anyone have any ideas or suggestions.

 

[FoFa]

Well the pupil has 240 mins of Mentor hours. Are you saying you want to record the pupil has a 120 min lesson, even thou it required 2 mentors? As I see it your problem will be, what if that same pupil had a 60 min tutor by mentora in one subject and another 60 min tutor by mentorB on a different subject the same day? Then what? Sounds like what you need is another table to record time tought, so in in your exampe you could record 120, and in my example you could also record 120. Now you could include a field on your current table, and break the time tought up between the mentors to come to total, but that is not the best choice because it envolves remembering how to do it correctly, rather than asking and recording it properly.

 

[The_Doc_Man]

From your description, it is (to me) a tossup as to whether your problem is a design issue or a "problem understanding" issue. What I mean is this: You can never do in Access that which you cannot even do on paper. You must embrace the problem before you can program it.

Having said that, I absolutely, categorically, and emphatically support your idea of paper first, programming later. A gentleman by the name of Ed Yourdon was famous many years ago for methods of designing programs BEFORE you tried to implement them. Having personally used one of his methods to build a commercial, multi-national database (wasn't Access, but the principle is the same), I can say that the more you do first on paper, the better off you are.

Now, let me toss some thoughts your way to hopefully help you see where you might be confused.

Student table, all details about student, nothing about mentors, nothing about sessions - CONCEPT CORRECT

Mentor table, all details about mentors, nothing about students, nothing about sessions - CONCEPT CORRECT

Mentor/student junction table - shows meetings between students and mentors - CONCEPT CORRECT - but a question: What does it mean to have a meeting? Because it is this fine point that is confusing you.

One line of thought: If mentor A is there for an hour, the mentor has one contact-hour with the student. If mentor B has another session with that student at a different time, that person has one contact-hour with the same student. If mentor B is there AT THE SAME TIME as mentor A - what does that mean? Do BOTH mentors have one contact-hour? Does the student have one or two mentor-hours? Remember that a contact-hour is a COMPOUND UNIT, not a simple one.

So your question has to be: Does having two mentors at the same time dilute the hours of contact for each mentor? Does having two mentors at the same time dilute the hours of contact for each student? (Obviously, when they are there a different times, no dilution occurs.)

Once you resolve this question IN YOUR MIND, you will be able to make sense of what it tells you.

Once more I emphasize: You are doing the right thing to work it out on paper first. (A) because the more questions you answer now, the fewer questions you face later; and (B) paper has a better memory than people, so KEEP YOUR NOTES FOR LATER!!!!!!

 

[cuttsy]

Thanks Doc.