Imagine a software for theater owners. You can accept reservations for the different cinema halls. Each hall has a different number of seats.
Say the owner wants to know how many people visited the cinema on each day in the past years. Easy, just query the Reservations table and group by date.
What if we want to know the utilized capacity – how much % of seats are sold? Easy, just join the reservations with the Cinema_Halls table, and divide the number of reservations by the number of available seats.
However, on August 1st, the owner decides to add more seats to one of the cinema halls, and updates the database accordingly. But now, all the utilization percentages before August 1st are invalid, as we do not have access to the historical number of seats.
There are three solutions that come to my mind:
- Add a new table, which tracks number of seats over time. Tuple: (HallID, numberOfSeats, validFrom, validUntil)
- Don’t modify the cinema_hall record, but add a new one with a different number of seats, and mark the old one as invalid.
- Use special temporal database features of your DBMS
(1) seems to be cumbersome. If you really want the capability to get all historical data, you would need a separate table for every single value that could change.
(2) is not how the user would want to work, and could introduce side effects.
(3) might not be supported by your database system.
What would be a good solution to solve this?
2
It seems you raised the question with the answer already:
From my point of view, the solution I would have tried is:
“… Add a new table, which tracks number of seats over time. Tuple: (HallID, numberOfSeats, validFrom, validUntil) …”
This way, you are also able to track when the number of seats was changed. Also in the code you are able to know the exact % of seats sold by each date. You will not depend on a specific Database feature (DB is a detail to the SW generally speaking).
I am not sure how marking the first entry as invalid would have helped, because if you do not track the dates you will not be able to know the capacity of a Hall at a given time. So this would void number 2.
4
You don’t strictly want to know the total number of seats, but the total number of seats available to be sold. For example, some seats may be in existence physically but unsellable for a given event. I’d be inclined to think about an “available seat” as a tuple of (hallId, seatId, eventId) – which you can pair up with a Reservation when it arrives. Given that, you can then sum up over a set of events, a date range (joining to Events), have variable pricing, multiple screenings per day, etc.
If you don’t want to track individual seats, then just store (hallId, eventId, seatsAvailable, seatsSold) or (hallId, date, seatsAvailable, seatsSold) for each event or day. There will potentially be some redundancy in the data compared to (1) but it will make queries a lot cleaner than handling date ranges.
Door number (2) is attractive if you can do it. The problem with many applications tracking long-lived facilities (theater halls and other building components, aircraft, cargo vessels, …) is that they assume those assets are invariant. They are not. As relatively inflexible and expensive capital assets, they are not changed very often–but they definitely do change over their entire life cycles, which span years to decades. Seats are added or removed, rooms are subdivided or combined or radically reassigned, and so on.
If you have a Cinema_Halls table that can record identity information (e.g. name of the room, its location in the building), lifecycle information (e.g. start and stop timestamp, or in databases like PostgreSQL that have slightly better temporal handling, an interval defining the service lifetime), their capacity and/or other interesting parameters (e.g. total number of seats, number of handicapped seats, usable area in square feet, maximum occupancy as decreed by the Fire Marshal, …), then your database accurately records the parameters for the hall as a function of time. It exposed a little more complexity/variability in your data model, but in ways that reflect reality; doing utilization and other joins against that facility table will be straightforward.
However, you may not be able to make such a significant schema change. In which case you must put the data externally. Option (1) is such an approach–in effect, a facilities description table that assumes “Hall 1” is itself invariant, but that some of its operational parameters vary over time. There are pros to this approach. Your other tables, for example, will simply reference hallID = 1 always, and not need to have items 1, 4, 17, 19, and 31 all be different instantiations of the logical “Hall 1” at different points in time. But the queries when you do want hall capacity information will be a little more complex. And if Hall 1 is at some point separated into Hall 1A and Hall 1B, the “halls are invariant” assumption breaks anyway.
If you cannot make Option (1) work as a separate table, alternatives are to encode the lifecycle information somewhere–e.g. stored procedures, or into static data embedded within your analytics functions/methods/objects. That’s an ugly, bifurcated, and somewhat fragile approach–but there are many cases where that kind of duct tape and baling wire solution the best you can do, given other practical / organizational constraints. Despite the genuine downsides of the implementation, on the plus side, the code asking for the required values (e.g. “number of seats in hall x at time t”) can often do so cleanly (because the number_of_seats function/method/procedure hides how the value is computed).
Your final suggestion, choice (3), is admirable but unworkable, if only because workaday databases provide such weak support to represent and compute temporal relationships, properties, and realities. PostgreSQL has a pretty decent interval data type. It’s rich, precise, and works only in PostgreSQL. If you’re using PostgreSQL, have at–but it’s non-portable. And an interval type, while a start, is hardly an entire “temporal reasoning” capability. With other databases, you’re even worse off. Oracle has two interval types, un-unified, of varying precision. Other databases like DB2 and MySQL support some interval-like computations in their SQL operators and functions for date/time objects, but have no first-class interval types.
So, in order of solution richness and elegance: options 2, 1, 3.