2

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.

  • A small unrelated suggestion: in the booking, it's not about totalPayment but about amountDue, isn't it ? And then you should be able to find out what's already paid and what's still outstanding. – Christophe Oct 19 '19 at 8:40
  • 1
    amountDue, yes. Agreed that I need to add some attributes about payments, thanks for your suggestion! – Lyequation Oct 19 '19 at 10:30
5

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.

You need to keep in mind that all these values (especially totalPayment) would affect the bookkeeping and what is (was) finally payed to the drivers.
The bookkeeping, and what was payed out to the drivers needs to be (and stay) consistent at any time.

  • 1
    Excellent ! Another alternative would be to put the rates in a time-dependent table to get the right rates. But still, this would not protect against changes in the calculation formula, nor retroactive correction of wrongly entered prices in the current period. So your proposal is the safest. – Christophe Oct 19 '19 at 8:37
  • Thanks for the clear explanation! I'll store the value of totalPayment as you suggested. – Lyequation Oct 19 '19 at 10:29
1

Just to reply to the title of your question: P. 147 of UML 2.5 states:

9.9.17 Property [Class]

...

isDerived : Boolean [1..1] = false

Specifies whether the Property is derived, i.e., whether its value or values can be computed from other information.

You would write a {isDerived} after the property name.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.