Start dates, end dates and history tables

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.