Hi,
I want to design a db for a small car hire, i am not sure about the entities, but this is how the system is:
a customer (s) call to book a vehicle (s) on a particular day (s) of the week or month to pick up at certain time
the rate for the vehicle could be different from small to medium size and length for
hire.
a customer can reserve on the phone many vehicle and a booking is for one vehicle to be pick up only, so each vehicle has one booking form, one pick time, date but could have
different rates.
pick up time could be allocated to more than one vehicle, i.e in one day and hour, there could be more than one vehicle to be picked up
I came up with these entities:
customer_tbl ( license_number PK,bank_account PK,name, tel, address)
the reason why I have two PK is because when we reserve over the phone we hardly have the licence_number but we get always the bank_account for reservation
vehicle_tbl(vehicle_reg PK,model,size,fuel,mileage,color)
rent_rate_tbl(vehicle_type PK,daily, weekly, weekend)
date_tbl(date PK,days)
booking_tbl (booking_id PK, licence_number FK, vehicle_reg FK, date FK, vehicle_type FK,pick_up_time, duration,month, returned_date)
pickup_tbl( pickup_id PK date FK)
time_tbl(pickup_id PK,pick_up_time)
relationship
customer_tbl 1:m booking_tbl
vehicle_tb 1:m booking_tbl ( but again this is how it should be, the reality is i can do it and it become m:1 which i stated i don't understand may be a fan trap?)
rent_rate_tbl 1:m booking_tbl
date_tbl 1:m booking_tbl
date-tbl 1:m pickup_tbl
it's a m:m
time_tbl 1:m pickup_tbl
I have some problems though with this, not only I am not sure about the implemented relationship but again with booking_tbl it can never have one to many to vehicle_tbl ( could that be because of the compounds keys in booking_tbl?) I tried it so many times to establish it but end up having the opposite.
the other problem is that i feel that i should have pickup_tbl, time_tbl and rent_rate_tbl but so far there isn't much use of them because it could be easier to insert for example the pick up time or the rent rate manually then by being already assigned? not sure of this again.
my desing is in it's first stage and i really want to base it on a solid faultless implementation for future and accurate use, can anyone really help please as I cannot see it through, thanks for your help and sorry for being so long, I tried to be clear in my problem, thanks