SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)

    Synthetic vs Natural..

    (Yeah, instead of using another person's question, i decided to make a seperate thread.)

    My thought for an example/question would be calendar entries in something like Google Calendar.
    Table fields:
    userid,startdate,enddate,entryname,entryinfo,alarm,repeat (...)

    Is there a natural key here? My thought says no, unless you force one on (userid,startdate,enddate,entryname).... but is that efficient? Does it violate the convention of avoiding business logic in keys (since theoretically, someone could create the same event twice)?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by StarLion View Post
    Is there a natural key here? My thought says no, unless you force one on (userid,startdate,enddate,entryname).... but is that efficient? Does it violate the convention of avoiding business logic in keys (since theoretically, someone could create the same event twice)?
    that's at least three different questions right there

    whether or not a natural key exists depends on whether you want unique rows in this table

    so (userid,startdate,enddate,entryname) looks pretty decent as a potential key

    if you want to allow your table to have the same entryname for the same date range for the same user (i.e. a duplicate entry) then that key isn't unique enough

    also, efficiency has nothing to do with uniqueness

    the "convention of avoiding business logic in keys" is a new one on me, i'm not sure what you meant by that

    so whether you want to allow a duplicate is what counts here, and you must pick your key column(s) accordingly

    notice that if you didn't want the same entryname for the same date range for the same user, then the key you suggested is a good candidate for the primary key

    but here's the thing -- suppose you were to use an auto_increment as the primary key for this table, you would still want to declare a UNIQUE key on (userid,startdate,enddate,entryname) as well!!

    does any of the above rambling help?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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