This has always been a bit of a philisophical conundrum for me, because I always think, "Yeah, dates are OK. No need for a synthetic there" but have not put my finger on the reason why.

Until now!

Well, maybe, let me float a theory past you guys and see what you think, 'cos I'm not sure if this is a flash of insight or just sophistry -- it could go either way.

Start with two points:
  • The difference between a natural key and a synthetic key is that a natural key has some meaning to the naked eye in isolation, and thus can be exposed directly to the user and "generated" by users. A synthetic key's meaning is only revealed through translation to a natural value.
  • If you change the value of a natural key, you're genera'ly not changing what that key value represents, eg. "Finland", you are just changing it's means of representation -- ie. some visible code value.

Now what is a date value in Oracle? It's some internal representation -- a string of digits I suppose. Let me float the idea that a date column is actually a synthetic key, but appears to be natural because Oracle uses one of a great many possible conversions to represent it as a naturalkey. So if your boss came in one morning and said, "Bad news lads, the business is changing the standard date format to be Julian", or you wanted to suddenly start representing month names in ... um ... Urdu, then just as in the case where a country changes its code we're not saying that the date itself has changed, just the "human-friendly" representation of it. You do this pretty simply through changing NLS settings -- internally if you have quality code that always uses a date format picture to say such things as
Code:
Where date_of_transaction < To_Date('01-Jan-2005','DD-Mon-YYYY')
then you don't even have to change that.

So here's my proposition: date-based keys are really synthetic, and that's why they appear to be an exception (and that's why representing them with a regular sequence-generated synthetic value makes no sense) -- it's the representation of them, with year-month-day or whatever, that is natural, and in effect Oracle is maintaining an internal and very flexible two-way translation table for you, accessed through the to_date() and to_char() functions (not literally a table of course, but with the same purpose).

Feedback, feedback! What do you think.