2 added 1345 characters in body
source | link

Definitively the second approach is easier to work with:

  • with only one date, i.e. the start date of a new price, you don't know which is theif a given price record is the one to use for a calculation on a given date:
    • you always have to read several records, accessed in a sorted manner to find out which price is to be used
    • even for the current price you can't be sure, as there could be already some records for planned price changes in the future
  • with start and end date, you work with some redundancy. But it's much easier to make relational queries that use the right price.

When using the second approach, reverting to a previous price has first to be analyzed from a business perspective:

  • is it about the old price but from a new date onwards ? In this case you just add a new price record as usual
  • is it an error, and the new record should be cancelled ? In this case it's more complex:
  • you have to find the previous price record and update it back by reseting the end date. Finding the previous record can either be used with a sequential access to decreasingly ordered price records, or just using the known end-date (as we know the start date of the price record to erase).
  • but you also have to find any stored calculation that used the wrong price and update it
  • of course, for the sakeof consistency the previous steps have to be performed within a single transaction.

For the indetermianted end-date date there are two schools:

  • the purist would leave the field null to show that this information is not known.
  • the pragmatist would put '31.12.9999' as end-date. This is not so great, but it allows for more consistency (end date always present) and flexibility (use of either start date or end date for sorting) in the queries. A major ERP vendor uses this approach routinely, which makes me say that it's a proven technique, even if not so beautiful as the null.

Definitively the second approach is easier to work with:

  • with only one date, i.e. the start date of a new price, you don't know which is the price record to use for a calculation on a given date:
    • you always have to read several records, accessed in a sorted manner to find out which price is to be used
    • even for the current price you can't be sure, as there could be already some records for planned price changes in the future
  • with start and end date, you work with some redundancy. But it's much easier to make relational queries that use the right price.

Definitively the second approach is easier to work with:

  • with only one date, i.e. the start date of a new price, you don't know if a given price record is the one to use for a calculation on a given date:
    • you always have to read several records, accessed in a sorted manner to find out which price is to be used
    • even for the current price you can't be sure, as there could be already some records for planned price changes in the future
  • with start and end date, you work with some redundancy. But it's much easier to make relational queries that use the right price.

When using the second approach, reverting to a previous price has first to be analyzed from a business perspective:

  • is it about the old price but from a new date onwards ? In this case you just add a new price record as usual
  • is it an error, and the new record should be cancelled ? In this case it's more complex:
  • you have to find the previous price record and update it back by reseting the end date. Finding the previous record can either be used with a sequential access to decreasingly ordered price records, or just using the known end-date (as we know the start date of the price record to erase).
  • but you also have to find any stored calculation that used the wrong price and update it
  • of course, for the sakeof consistency the previous steps have to be performed within a single transaction.

For the indetermianted end-date date there are two schools:

  • the purist would leave the field null to show that this information is not known.
  • the pragmatist would put '31.12.9999' as end-date. This is not so great, but it allows for more consistency (end date always present) and flexibility (use of either start date or end date for sorting) in the queries. A major ERP vendor uses this approach routinely, which makes me say that it's a proven technique, even if not so beautiful as the null.
1
source | link

Definitively the second approach is easier to work with:

  • with only one date, i.e. the start date of a new price, you don't know which is the price record to use for a calculation on a given date:
    • you always have to read several records, accessed in a sorted manner to find out which price is to be used
    • even for the current price you can't be sure, as there could be already some records for planned price changes in the future
  • with start and end date, you work with some redundancy. But it's much easier to make relational queries that use the right price.