Why 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.

▲

advisedwang 5 hours ago | parent | next [-]

JOINs and other operations become really difficult if you can't evaluate whether a row applies or not based on that row alone.

▲

throwaway7783 6 hours ago | parent | prev | next [-]

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

▲

promano 6 hours ago | parent | next [-]

And even if you don't have seasonal products, you still need an end date to mark when you stop selling a product; otherwise you have to do something hacky like defining "<price> = NULL means we stopped selling the product after <valid_from>" and inserting an extra record.

I think the end date should be nullable though, but valid_to is NOT NULL in the starting example... later in the article, when showing the "new way" using date ranges, it inserts a row with an open-ended range, which is more what I'd expect.

▲

larsnystrom 6 hours ago | parent | prev [-]

You could also make the price column nullable and just insert a row with price null and the date from which there should be no price.

	▲	throwaway7783 5 hours ago \| parent [-]
		Yeah, but now this is implicit information (no price == marker row) and duplicate the entire row. And it is baking in "price" as the special field. This may not be just one field for another temporal entity

▲

yourMadness 4 hours ago | parent | prev | next [-]

If you sell a product where the customer can buy the future version today (for delivery in the future), that doesn't work.

▲

quotemstr 6 hours ago | parent | prev | next [-]

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.

	▲	larsnystrom 6 hours ago \| parent [-]
		Sure, from a theoretical perspective, but in practice there’s got to be some sort of order at some point even when storing timespans.

▲

warmwaffles 5 hours ago | parent | prev [-]

It's a contrived example.