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