Q
Problem solve Get help with specific problems with your technologies, process and projects.

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.

This was last published in October 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close