I really appreciate your posts and keep them coming! I would like to learn more from you in regards to your answer Using date as a primary key (04 November 2005). I found what you said very enlightening. I hadn't realized the Gregorian calendar was so long in the making! I'm fairly new to all this and was wondering if the person who asked the question gave you details on the purpose of his database. Was it to store a unique item for a day in time? Wouldn't using the date in a database only allow one entry for a particular day? I was wondering about instances where multiple items for a day would be stored. What if the database was used to track items sold for a day? Would it only be able to store one of the items sold on that day? Sure appreciate all your insights! Thanks.
First of all, thanks for the kind words. Thank you for taking the time to write, and to ask for further clarification.
That particular column created quite a bit of feedback. Several people contacted me to point out what was wrong with my idea of using a date as a primary key. After an exchange of emails, one person even submitted this Tip to the SearchOracle site:
As I said in my rebuttal:
I chose to interpret the original reader's question about using a date as a primary key, to mean a value such as 2005-11-17. In standard SQL terminology, this is a date value. You were perhaps thinking of datetime values, which include hours, minutes, seconds, and some fraction of seconds, but I was speaking only of date values.
If you would kindly re-read my column with this distinction in mind, perhaps it will make more sense. I am sorry I was not sensitive enough to the fact that so many Oracle people might be misled (you were not the first to raise an issue with this column).
I'm sincere about that last part. I should have realized that my column, despite being about standard SQL, is located on the SearchOracle site, and that many readers will perhaps know only Oracle SQL. I'll try to keep this in mind from now on.
The purpose of the original reader's database was not stated. The question merely asked, "If I select the date as a primary key, what problems could I face in the long term?"
You asked: "Wouldn't using the date in a database only allow one entry for a particular day?" The answer to this is unequivocably Yes!!
Since the purpose of a primary key is to provide a unique way of identifying every occurrence of an entity, let's focus carefully on what that entity might be.
You further asked: "What if the database was used to track items sold for a day? Would it only be able to store one of the items sold on that day?" If date were the primary key, then of course, you're right, this would allow only one sale per day. What this tells us is that for this scenario, date is not a suitable primary key.
What would be a good primary key for sales? Well, a surrogate sequential key would work, or we might want a composite primary key consisting of the item number, store number, cash register number, and date, or perhaps better yet, date and time. But that wasn't the original question.
So for which kind of entity would date be suitable as a primary key? The answer is: days. If we need some way to uniquely identify each day, the Gregorian date is the best choice. Each date can be associated with exactly one day, and vice versa.
Could we use some other way to identify each day? Sure. We could, for instance, sequentially number each day from a specific starting point. In this scheme, today might be called 5383936, tomorrow would be 5383937, the next day would be 5383938, and so on.
But such a scheme would have drawbacks.
"Todd, when did we say we'd have that report ready for publication to the shareholders?"
"Um, let's see, ah, here it is, boss, we're going to publish it on 5384117."
"You blockhead, what day is that?"
"Hold on, boss, let me call the DBA."
If we need to include dates in the past, we might want to consider Julian Day Numbers, which astronomers use to avoid ambiguity between Gregorian or Julian dates. Interestingly enough, Julian day numbers are sequentially numbered, starting from a specific "Day 1" which is many thousands of years ago.
But going forward, if the table will have no ambiguous dates, the Gregorian date is an excellent choice. If there is anyone reading this who feels the need to use a surrogate key instead of the Gregorian date to uniquely identify each day, I would very much like to hear your reasons.
This was first published in December 2005