To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
I don't see any, but before we discuss using date as a primary key,
let's make a couple of assumptions about the dates that we're
planning to store in our table.
First, let's assume that we're using the
Gregorian calendar.
As you may know, this calendar was adopted to replace the Julian calendar
in most Western countries, but they did not all do it on the same day.
The change has taken hundreds of years, between 1582 and the twentieth
century, and during that time,
what was May 13th for one person was May 24th for someone else. So the second
assumption is that we're going to store dates in our database only
from the current era. This is because a date in any of those earlier years
is not enough to pin down exactly what day it was; more information would be needed,
such as whether it is a Gregorian or Julian calendar date.
You see, that is the purpose of a primary key: to give identity
to a real-world entity (thing, event, etc.). What we are identifying when
we use a date is a particular day.
Let me give you a real example. Suppose
we were interested in the day when we will next have a full moon on an equinox.
We may not know what date that is, but it definitely is a single, particular day.
It has identity. Even if we don't know what its date is, we do know that it
exists (has identity in the real world, albeit in the future) because we know enough
about celestial mechanics to be 100% positive that there will definitely be another
full moon on an equinox, some day in the future.
With me so far?
Okay, now, the only two requirements for a primary key are:
- it is never null
- it uniquely identifies each row in the table
On this basis, a date makes an excellent primary key, provided
we know which calendar we're using. And if we assume Gregorian, and we don't
need to store historical dates, we're fine. Note that with date as the
primary key, we could not enter the day of the next full moon on an equinox into
our new table, until we actually compute the date, which cannot be null
since it's the primary key.
Okay, you did ask whether there would be any problems in the long term.
In the long term, we could theoretically change calendar systems. We could
have thirteen months of twenty-eight days—accountants would love that
setup—with one or two extra "intra-annual" days thrown in each year
to make the total come out to 365 or 366 as necessary. Nothing stopping us.
Well, except perhaps the worldwide investment in existing computer systems.
You thought getting ready for Y2K was expensive? Imagine switching calendars!
My personal bet is that we will never get off the Gregorian calendar, at least
not before you and I and everyone we know is long, long gone, and intelligent
computers that can reprogram themselves have been invented.
Of course, if you want to be safe, to ensure that your database survives
even beyond that far off day, you could use a surrogate key.
A surrogate key is an artificial key, like a distinct integer, which takes the place
of the primary key. This way, the "real" key, the date in this instance, would
be stored in a lookup table, and you would reference all rows to the lookup table
using the surrogate key, to find out what the date is. Thus, you would store
Fred's birthday not as 2937-12-23 but as something like 4529374.
Then, if the date ever has to change, like in a change from Gregorian to some
new calendar system, e.g., from 2937-12-23 to 2937-13-21, then you would simply update
the lookup table and none of the related tables would have to be updated because they
are using the surrogate key instead! Fred's birthday would still be 4529374!
This (using a surrogate key) is exactly the advice many DBAs will give you.
I should emphasize at this point that I am not a DBA, I am a data modeller, and
in my opinion, using dates as a primary key is perfectly safe, at least as long as
the original assumptions are true, that they are Gregorian dates and do not
include ambiguous dates from the past. If those assumptions are true,
using a surrogate key would be silly.
|
