Date as a part of primary key

[anujdutt]

Hi All,
I want to know are there any benefits to use date as a part of my primary key?
For example in a transaction tables I have many columns and transaction date is one of them and primary key as

This first 8 letters are date in yyyymmdd format and later on some sequence.


20030324001
20030324002
20030324003
20030324004
20030325001
20030325002
.
.
.


Few database designers want this kind of design for there table's primary key. If someone of you ever designed your database tables like this please let me know the precise benefits of this kind of design.

[slimdave]

There's two issues here ... should the date be stored as text, and should the date be part of the PK.

Answer to first question: Definately not ... store dates as dates.

Answer to second question: Maybe, if you are absolutely sure that the date value is never going to be modified.

[anujdutt]

I will keep date in seperate column with date datatype as well as a part of primary key.

[DaPi]

Originally posted by slimdave
. . . . if you are absolutely sure that the date value is never going to be modified.

It will be.

After making many mistakes over many years I am convinced that PK's should always be meaningless. Yes, yes, yes, there are rare exceptions - but so rare that making the "wrong" decison isn't worth worrying about.

[slimdave]

A case where it probably wouldn't change would be such things as date-based lookup tables, which you might want to be index-organized.

[DaPi]

. . . and then you realise you've been running the server with the wrong date . . . (Yes, I've seen it happen.)

[slimdave]

Originally posted by DaPi
. . . and then you realise you've been running the server with the wrong date . . . (Yes, I've seen it happen.)

In the case of a lookup table though, I'm prepared to abandon my not-so cherished principles on synthetic keys for two reasons ...

• There will almost certainly be no other tables referencing the PK, so cascading updates are no problemo
• I'd probably be looking for the fastest possible date-based access, which means an IOT, and date would have to be part of the PK to get the benefit