Is it allowed to calculate the derived attributes by using other tables' attributes directly?

by Lyequation   Last Updated October 19, 2019 08:05 AM - source

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:

ER diagram GRD

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.

Answers 1

One problem I see with the approach to calculate the totalPayment on the fly that way, is if the Driver.baseRate or 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.

πάντα ῥεῖ
πάντα ῥεῖ
October 19, 2019 07:02 AM

Related Questions

Database Modeling doubt

Updated December 10, 2017 16:05 PM

how to correctly normalize my database

Updated March 09, 2018 17:05 PM