ansaurus

Question

What is the best way to handle validity dates in applications ?

Answer 1

+1 A:

If a price should always be in effect, then I would use a single column and use queries to determine the expiration.

Create Table Prices
(
    EffectiveDate datetime not null
    , Price decimal(15,4) not null
    , Constraint UC_Prices_EffectiveDate Unique ( EffectiveDate )
)

Select P1.EffectiveDate As Start
    , Coalesce(
                    (
                    Select Min(P2.EffectiveDate) As EffectiveDate
                    From Prices As P2
                    Where P2.EffectiveDate > P1.EffectiveDate)
                    ), '9999-12-31') As End
    , Price
From Prices As P1

This later query you could put in a View so that can easily be used in other queries. If you try to store both the start and the expire, then you need to add a bunch of code, probably in a trigger, than ensures you do not have an overlap nor have a gap.

Thomas 2010-04-06 04:23:07

+1. Don't try to add ranges, just add the places where the values change.

Adam Musch 2010-04-06 13:11:15

This is an interesting thought, I never thought that I could do away with the expiry date.

panzerschreck 2010-04-06 16:29:36

The basics to this type of decisions is, you can figure it out every time you query or you can figure it out once and store the value. You will do more work with each query if you use the one column approach. You also have fewer issues to manage around non-continuous or overlapping sections. If the process that creates the records is rigorous and query time is key, then use 2. If the process is user driven and you query infrequently, use 1. There's no single correct answer.

Stephanie Page 2010-04-06 22:17:23

Just to add some data, I built this example and it took 4x the number of block reads to get the rows from the query above than from the same table with a between on two date columns

Stephanie Page 2010-04-06 22:35:20

@Stephanie Page - It is not clear how the data will actually be used. If all that is needed is the effective price as of some target date, then the expiration would never need to be calculated. You could simply find the earliest effective date greater than or equal to your target date and that query would be substantially quicker.

Thomas 2010-04-06 23:34:50

Answer 2

A:

I'm assuming that there are also other columns in play that identify the thing which' price is being recorded.

You should be extremely careful with the system doing all sorts of update automatically.

If a user registers a new price for THING1, but does so accidentally (because he intended to register a new price for THING2) : the 'current' price for THING1 is set expired, but will that also be automatically undone when the error is spotted and corrected ?

If you think you can do that, how are you going to get the old expiry date back (you can't be certain that it was null, because your business might be such that the expiry date had already been set to some known date in the future) ?

How are you going to respond to requirements for retroactive updates ?

Etc. etc.

Erwin Smout 2010-04-06 09:15:58

I do agree automatic updates are annoying to the user, but can you think of something that will accept the user input and at the same time keep your system consistent ?

panzerschreck 2010-04-06 16:31:06

ansaurus

tags:

views:

answers:

What is the best way to handle validity dates in applications ?

related questions