I tried to build a database for Driver Booking system. Here's brief details:
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.
One problem I see with the approach to calculate the
totalPayment on the fly that way, is if the
Vehicle.hourRate would be changed at a later point of time, the formerly calculated value couldn't be reproduced.
I'd prefer to keep track of the totalPayment in a different table, or store it as a manifested attribute in the
Booking table. For filling the data in that table you still can use calculated values from a
SELECT statement, but these are only valid in conjunction with a particular point of time when they were calculated.