Date as a part of primary key
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Date as a part of primary key

  1. #1
    Join Date
    Dec 2001
    Location
    Edmonton, Canada
    Posts
    50

    Question Date as a part of primary key

    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.
    An elephant is a mouse with an operating system.
    Share on Google+

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE
    Share on Google+

  3. #3
    Join Date
    Dec 2001
    Location
    Edmonton, Canada
    Posts
    50
    I will keep date in seperate column with date datatype as well as a part of primary key.
    An elephant is a mouse with an operating system.
    Share on Google+

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.
    Share on Google+

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE
    Share on Google+

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    . . . and then you realise you've been running the server with the wrong date . . . (Yes, I've seen it happen.)
    Share on Google+

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE
    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width