Access relationship..the best way...

[Tech]

Hi there

I am trying to make a simple "booking" system.

This booking system is for cars.

We have customers, cars availible and the date and time and location etc... of when to pick up the customer

So 4 tables i have so far:

cars
customers
Bookings
Drivers

a field in Bookings known as customerID is joint to the customers table, field CustomerID.
a field in the Bookings known as DriverID is joint to the Drivers table, field DriverID
a field in Bookings known as CarID is joint to the cars table, field CarID

I am wondering, does this sound ok to you? i am not great with relationships but i am improving. does this sound ok?

Another Q is, i need to know what times of bookings are availible. What is the best way of going about this?

Thanks

 

[Pat Hartman]

The key to the bookings table needs to be CarID and RentalDate. CustomerID is a data field. DriverID has a 1-many relationship with the booking since more than one driver may be authorized to drive the rental car. That means that you need an additional table.

 

[Tech]

i have all that yay i was right!

 

[Pat Hartman]

Well we couldn't tell that from what you posted

 

[Tech]

heh yeh - sorry my bad. was all over the place!

still trying to figure out a way to calculate the cost as well as displaying dates availible to book a particular car.

here is the database relationship:

Drivers:
DriverID (PK)
Driver_fname
Driver_lname

Prices:
CarID
Price_Per_Hour

Cars:
CarID(PK)
Car_Manufacturer
Car_Model
Car_Reg

Customers:
CustID(PK)
Cust_fname
Cust_lname
Cust_Address
Cust_Town
Cust_Contact_No

Bookings:
BookingID(PK)
CarID
CustID
AdditionalNotes
FarePrice << Meant to be the total price
Booked
DateOfPickUp
TimeToPickUp
PickUpAddress
DropOffAddress
DriverID
Hours_Req

relationships:

TableName: FieldName > TableName: FieldName
Cars: CarID > Bookings: CarID >> 1:m
Customers: CustID > Bookings: CustID >> 1:m
Drivers: DriverID > Bookings: DriverID >> 1:m

hope it helps...

 

[Pat Hartman]

In the real world it is possible to have more than one authorized driver so the relationship between bookings and driver is m-m rather than 1-m so you need an additional table to handle the relation and you need to remove driverID from the bookings table.

Usually the daily rate is associated with the car itself. When the booking is made, this can be copied to the bookings table. The total will not be stored, just the daily rate. This rate can be overwritten when the booking is made if necessary. If there are taxes that apply, the tax rates need to be stored somewhere and the applicable tax rate also needs to be stored in the bookings table.

If you need to handle different rate schedules - hourly, daily, weekly, monthly, the problem is more complicated. To minimize updating, usually cars are grouped into classes and a rate schedule is assigned to each class. So the PriceUnit (hour, day, etc) must be stored along with the going rate at the time of booking. The total is calculated by multiplying the number of units times the price per unit times the tax.

In a database, tables normally contain data for what is rather than what is not. That makes it easier to find booked periods rather than unbooked periods. Your query needs to look for a specific car class rather than a specific car and find records for cars booked during the period. That query is then used to find cars of the same class that don't appear in the already booked recordset.

 

[Tech]

thanks very much

i am not bothered about the "real world" stuff really, it's just a simple quickie little database, however i have learnt and thought more about what you have said Thanks alot! Much appreciated