Relationships / Normalisation

[Emlou85 via AccessMonster.com]

Hi

I'm trying to set up a database but I'm not sure that I've got the right
fields in the right tables and I'm not sure that my relationships are correct
either. Could someone help me please!

tblTest
Test ID (P)
Licence Number
Date
Type of Test
Passed?

tblCustomer
Licence Number (P)
Surname
Forename
Address 1
Address 3
County
Postcode
DOB
Telephone No.
Enrolled on pass plus

tblBooking
Booking ID (P)
Instructor ID
Licence Number
Time
Date
Location Pick up
Location Drop Off
Single/Double Lesson
Customer Paid?

tblInstructor
Instructor ID (P)
Surname
Forename
Address 1
Address 2
County
Postcode
Date of Birth
Telephone Number
Price per lesson

Currently I have the following relationships:

tblCustomer - Licence Number - tblTest - Licence Number (one to many)
tblCustomer - Licence Number - tblBooking - Licence Number (one to many)
tblInstructor - Instructor ID - tblBooking - Instructor ID (one to many)

However, I think I must be going wrong somewhere because if i want to find
all the people who have passed their theory test and for the query to also
display the name of their instructor the results are incorrect. I was
wondering if I needed to have the Instructor ID as a foreign key in
tblCustomer but then presumably there would be no link between tblInstructor
and tblBooking.

Hope someone can help! Thank you in anticipation.

Emma

 

[Martin Fishlock]

Emma,

I think that you need some link between the customer and the instructor.

I'm not sure how your model works but it could be that a perosn has passed
the theory but has had no lessons (new joiner or transfer) also the person
may have had more than one instructor. So you can keep the current instructor
in the customer table.

You may also want to keep the theory and practicle test passes in the
customer file for ease of checking.

It really depends on how you like to design. Some redundency makes life
easier but you have to maintain the duplicates put passes are generally
sunken data.

 

[Tim Ferguson]

Sorry: I've realigned these to make them a bit more readable and
to avoid Death Characters like spaces inside object names.
A * is a primary key and a + is a foreign key.

Tests
*TestID, LicenceNumber+ TestDate, TestType, PassedOrNot

Customers
*LicenceNumber, Surname, Forename, etc, EnrolledOnPassPlus

Bookings
*BookingID, InstructorID+, LicenceNumber+, BookDateTime, PickUp,
DropOff, LessonType, PaidOrNot

Instructors
*InstructorID, Surname, Forename, etc, PricePerLesson

However, I think I must be going wrong somewhere because if i want to
find all the people who have passed their theory test and for the
query to also display the name of their instructor the results are
incorrect.

You don't have any concept here of a driver's instructor. Each Customer
will have a whole bunch of Booking records, which may or may not belong
to the same Instructor.

You can make the query read the _most recent_ instructor.

Otherwise you'll need a new field in the Customers table to record
AllocatedInstructor or something to say who the MainMan (or woman) is.
It's presumably not the same thing as who the Customers are actually
having their Bookings with -- what happens when a Customer's allocated
Instructor is away on holiday or maternity leave etc etc?

Which you choose is down to your business rules, and what makes sense
there.


Hope that helps


Tim F

 

[Amy Blankenship]

This is not related to your question, but you can avoid the practice of
putting spaces in field names, yet not have to change the captions on all
the fields when you set up a form by using the caption property of the field
when you design the table.

HTH;

Amy