Synthetic vs Natural..

[StarLion]

(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)?

[r937]

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?