I tried to build a database for Driver Booking system. Here's brief details:
- Drivers have several vehicles and they offers the driving service with them
- Drivers will charge different hourly rates on the different vehicles
- Drivers have base charge for each booking, this is independent of the vehicle types but depends on the individual drivers
- Booking need to keep track of venue, date and time, hour will be spent and total payment due
- The database should have the details of Venue as well
With these detail, I built an ER diagram and global relation diagram:
I noticed that the total payment attribute on Booking would be a derived attribute as
Driver.baseRate + (Vehicle.hourRate * Booking.hour).
I could show that column with
SELECT simply but what should I do if I am trying to use the totalPayment value later (such as to get total spent amount of client etc.)? Should I connect Driver and Booking then put copies of hourRate and baseRate as foreign key to calculate a derived attribute from them? I tried to research about this but many examples explained the derived attributes by calculating it with the attributes from the same entity/table.