Start dates, end dates and history tables
I have a database with several tables that use a composite primary key of ID, start date, end date. If I am changing any description on a particular table, I will update the start date and end date, then I will insert the new row. Is there any problem if I am updating the composite primary key?
I have a database with several tables that use a composite primary key of ID, start date, end date. If I am changing any description on a particular table, I will update the start date and end date, then I will insert the new row. This is because of keeping a history. Is there any problem if I am updating the composite primary key?
This is a particularly interesting question because there are so many possibilities as to what might be going on.
Let's say there is a row like this:
ID StartDate EndDate Description 27 2007-11-15 2007-11-30 Something Nice
The question now is, what exactly are you doing when the description needs to change? In your original question, you say you update the start and end dates, and then insert the new row.
But this sounds wrong to me. Let's walk through a typical scenario.
We know that ID 27 has the description "Something Nice" for the period of November 15th through 30th, 2007. Let's say that on December 1st, we want the description to be "Something Different" until December 15th. So we simply insert another row.
ID StartDate EndDate Description 27 2007-11-15 2007-11-30 Something Nice 27 2007-12-01 2007-12-15 Something Different
See, we haven't updated anything. To answer your question, yes, you are allowed to update a composite primary key. However, there is no need to do so here.
But wait. Maybe the "old" row is then removed. This would mean that you are actually keeping the history rows in a separate history table, as opposed to right there beside the "current" row. If this is the case, why do the detail tables (of which you say there are several) include the start and end dates? They would only need the ID and Description columns, because the separate history table would keep track of the dates.
I've seen both approaches to the problem. It gets even more complicated when you have the situation where a value remains "current" until changed, i.e. with an open-ended end date. In these cases, it is better to use NULL for the end date, and not, contrary to what you may have seen, some "arbitrarily high" fictitious end date like 9999-12-31. This means that the end date column cannot be part of the primary key, which may not be NULL (in any of its columns, if it is composite). But that's okay, because you only really need the start date as part of the PK. Presumably no time intervals will overlap.
History tables are so interesting because there are so many ways they can be designed.
