alt Hacker NewsWhy are they storing a time period (start and end date) in the first example? Why not just store the date when the price comes into effect? That would make both overlaps and time travel impossible without using any constraints.
Replies
Works when there is always an active price. Having an explicit end date allows certain rows to be inactive automatically after validity period. Think of seasonal categories/products etc which dont exist after a specific period
If you sell a product where the customer can buy the future version today (for delivery in the future), that doesn't work.
It's a trade-off. If you store both endpoints you can continue to think of rows as order-invariant tuples. If you store only one endpoint, you have to impose a meaningful order on the rows in order for them to make sense.
It's a contrived example.
JOINs and other operations become really difficult if you can't evaluate whether a row applies or not based on that row alone.