0

I need to record changes to a particular value, which is read as part of multiple events that are all separately tracked, and be able to view a log of all of the values later.

I think an example will describe the problem better: Let's say I'm designing a database for a rental car company. The company has a list of all of their cars, and they want to track the odometer reading of each car as often as possible. So, the company has the odometer read as part of a number of different events that are also recorded, such as renting, transfers, maintenance, etc.

The database might look something like this (note how OdometerReading is repeated):

Cars             Rentals             Transfers           Maintenance
====             =======             =========           ===========
Car_ID           Rental_ID           Transfer_ID         Maintenance_ID
SerialNumber     Car_ID              Car_ID              Car_ID
...              RentalDate          TransferDate        MaintenanceDate
                 OdometerReading     OfficeFrom          Notes
                 ...                 OfficeTo            OdometerReading
                                     OdometerReading     ...
                                     ...

Is this a good route to continue down? It seems odd to have the same field (OdometerReading) in different tables like this.

I need to be able to generate a report showing the history of the odometer readings over time.

I could write a query such as:

SELECT RentalDate, OdometerReading
FROM Rentals
UNION ALL
SELECT TransferDate, OdometerReading
FROM Transfers
UNION ALL
SELECT MaintenanceDate, OdometerReading
FROM Maintenance
ORDER BY RentalDate

Or, I could remove the OdometerReading field from all the tables and have an odometer readings table like this:

Rentals                 OdometerReadings
=======                 ================
Rental_ID               OdometerReading_ID
Car_ID                  Date
RentalDate              Mileage
OdometerReadings_ID
...

--(other tables removed for brevity)

This way, when viewing all the rental events, the odometer reading can still be included.

But this seems to give me other problems. The date is now recorded in two places (e.g., Rentals.RentalDate and OdometerReadings.Date), and it seems more difficult to determine where an odometer reading came from (although I believe you could left join the OdometerReadings table with potential source tables and filter where the right table is null).


What is a good route for recording the same value as part of a number of different records/events, each with their own table?

1 Answer 1

2

Another option is to use simple table inheritance for your events, with the event as the base type:

CarEvents              RentalEvents
=========              ============
ID <------------------ EventID
EventType         |    CustomerID
CarID             |    RentalDetail03
EventDate         |         ...
MilageReading     |    RentalDetailNN
GeneralDatail05   |
    ...           |
GeneralDatailNN   |    TransferEvents
                  |    ==============
                  `--- EventID
                  |    SourceOfficeID
                  |    DestinationOfficeID
                  |    TransferDetail04
                  |          ...
                  |    TransferDetailNN
                  |
                  |
                  |    MaintEvents
                  |    ==============
                  `--- EventID
                         ...

This means that queries about general properties (event date, car involved, etc.) only hit one table rather than needing to union the distinct sources, without duplicating the general details. Of course to report on a given event type you need to join in the relevant sub-table, and to report on multiple event types you have to join in multiple sub-tables.

Some databases have built in support for this form of inheritance which makes it behave a little more automatically (a bit like partitioning), meaning you have slightly less work to do as a developer, but this is essentially syntactic sugar and as you can see the basic structure is not at all rocket science.

3
  • 1
    I'm with you on this. A bonus to this is when pulling a history on the car, you can just get a list of events, dates, and event types, and just join on the sub tables Jan 3, 2020 at 12:59
  • This does seem to be a good idea. Thanks for your time spent helping with this! This way seems similar to the second option I was considering, but this is more insightful by identifying the inheritance nature of the problem-- I needed to flip the way I was thinking around.
    – elmer007
    Jan 3, 2020 at 16:02
  • When you mentioned "table inheritance", I got curious and ended up finding this other answer that is related: stackoverflow.com/a/3579462/5687262
    – elmer007
    Jan 3, 2020 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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