Thread

1. Re: Surrogate keys (Was: enums)

   Pollard, Mike <mpollard@cincom.com> — 2006-01-19T14:37:12Z

   Martijn van Oosterhout wrote:
   
   > Please provides natural keys for any of the following:
   > 
   > - A Person
   > - A phone call: (from,to,date,time,duration) is not enough
   > - A physical address
   > - A phone line: (phone numbers arn't unique over time)
   > - An internet account: (usernames not unique over time either)
   
   Ahh, a challenge.  Hmm, not sure about all of them, but here goes:
   
   A Person - well, you could use a bit map of their fingerprints, or maybe
   their retinal scan.  Of course, that could change due to serious injury.
   Maybe some kind of representation of their DNA?
   
   A physical address - how about longitude/latitude/height from sea level?
   
   The point here is two-fold.  First, what we call 'natural' is frequently
   itself a surrogate key (yes, even your name is really just a surrogate
   key.  As with all surrogate keys, it is a sequence of symbols that you
   use to represent yourself).  The second point is even when you find a
   truly 'natural' key (something not arbitrarily made up by anyone, and
   uniquely identifying the data in question), it may be completely and
   utterly inappropriate to use in a database.
   
   What is 'natural' anyway?  If someone phones in an order, we usually
   assign an order number to that request.  This order number is not the
   actual order, and the customer couldn't care a less what it is, but I've
   never heard a DBA argue we should get rid of it (well, to be fair, I've
   never discussed order numbers with a DBA at all).  After all, would it
   make sense for the key for that order to be the customer's name, the
   date/time of the order, all the items ordered, and the address to ship
   the order?  That isn't a key, but it's the only 'natural' thing that
   identifies that order that immediately comes to my mind.
   
   On the other hand, would anyone argue that an order_item table should
   have a surrogate key?  Well, I wouldn't.  The key for the order_item
   table should be something like the order number and the inventory item
   number together (IMHO).
   
   The point?  Surrogate keys and natural keys are two tools in the
   database arsenal.  Just as it is unwise to use a hammer to drive a screw
   just because you don't believe in screwdrivers, it is unwise to just off
   hand discard either method of specifying a key.  Rather, use
   intelligence and education (one of which is discussions such as this) in
   deciding how best to represent your data to aide in performance, ease of
   use, and adaptability.
   
   Mike Pollard
   SUPRA Server SQL Engineering and Support
   Cincom Systems, Inc.
   
   
   
   

2. Re: Surrogate keys (Was: enums)

   Mark Mielke <mark@mark.mielke.cc> — 2006-01-19T14:53:11Z

   On Thu, Jan 19, 2006 at 09:37:12AM -0500, Pollard, Mike wrote:
   > Martijn van Oosterhout wrote:
   > > Please provides natural keys for any of the following:
   > > - A Person
   > > - A phone call: (from,to,date,time,duration) is not enough
   > > - A physical address
   > > - A phone line: (phone numbers arn't unique over time)
   > > - An internet account: (usernames not unique over time either)
   > Ahh, a challenge.  Hmm, not sure about all of them, but here goes:
   > A Person - well, you could use a bit map of their fingerprints, or maybe
   > their retinal scan.  Of course, that could change due to serious injury.
   > Maybe some kind of representation of their DNA?
   
   Yes. Representation of the DNA is probably best. But - that's a lot of
   data to use as a key in multiple tables. :-)
   
   > A physical address - how about longitude/latitude/height from sea level?
   
   Planet? Solar system? Galaxy? Universe? :-)
   
   I agreed with what you had to say (the stuff I deleted). Just felt
   like being funny. Not sure if I'm successful. Hehe...
   
   Cheers,
   mark
   
   -- 
   mark@mielke.cc / markm@ncf.ca / markm@nortel.com     __________________________
   .  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
   |\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   | 
   |  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada
   
     One ring to rule them all, one ring to find them, one ring to bring them all
                          and in the darkness bind them...
   
                              http://mark.mielke.cc/
   
   
   

3. Re: Surrogate keys

   Chris Browne <cbbrowne@acm.org> — 2006-01-19T15:42:42Z

   mark@mark.mielke.cc writes:
   
   > On Thu, Jan 19, 2006 at 09:37:12AM -0500, Pollard, Mike wrote:
   >> Martijn van Oosterhout wrote:
   >> > Please provides natural keys for any of the following:
   >> > - A Person
   >> > - A phone call: (from,to,date,time,duration) is not enough
   >> > - A physical address
   >> > - A phone line: (phone numbers arn't unique over time)
   >> > - An internet account: (usernames not unique over time either)
   >> Ahh, a challenge.  Hmm, not sure about all of them, but here goes:
   >> A Person - well, you could use a bit map of their fingerprints, or maybe
   >> their retinal scan.  Of course, that could change due to serious injury.
   >> Maybe some kind of representation of their DNA?
   >
   > Yes. Representation of the DNA is probably best. But - that's a lot of
   > data to use as a key in multiple tables. :-)
   
   That is arguably about the best choice possible, for a human being, as
   DNA isn't supposed to be able to change (much).  
   
   Mind you, there do exist odd cases where a person might have two sets
   of DNA in different parts of their body.  This commonly (well, it's
   not really very common...) happens when non-identical twins share a
   blood supply; that twins were involved may not be noticed if one does
   not survive to birth...
   
   >> A physical address - how about longitude/latitude/height from sea level?
   >
   > Planet? Solar system? Galaxy? Universe? :-)
   >
   > I agreed with what you had to say (the stuff I deleted). Just felt
   > like being funny. Not sure if I'm successful. Hehe...
   
   Well, that's useful for representing a key for a piece of real estate.
   
   It's fairly interestingly useless for representing a human attribute,
   at least in terms of being a primary key...
   -- 
   output = ("cbbrowne" "@" "acm.org")
   http://cbbrowne.com/info/linux.html
   "It's a pretty rare beginner who isn't clueless.  If beginners weren't
   clueless, the infamous Unix learning cliff wouldn't be a problem."
   -- david parsons
   
   

4. Re: Surrogate keys (Was: enums)

   Jim Nasby <jnasby@pervasive.com> — 2006-01-19T17:42:31Z

   On Thu, Jan 19, 2006 at 09:37:12AM -0500, Pollard, Mike wrote:
   > The point?  Surrogate keys and natural keys are two tools in the
   > database arsenal.  Just as it is unwise to use a hammer to drive a screw
   > just because you don't believe in screwdrivers, it is unwise to just off
   > hand discard either method of specifying a key.  Rather, use
   > intelligence and education (one of which is discussions such as this) in
   > deciding how best to represent your data to aide in performance, ease of
   > use, and adaptability.
   
   There is one thing to consider: consistency. If you mix and match
   'natural' keys and surrogate keys as PK, then how do you know which one
   you're supposed to be joining on? How does everyone else on the team
   know?
   
   Sure, there's many examples where you don't really need a surrogate key.
   But there's just as many (if not more) where you want a surrogate key so
   that you don't have to deal with the pain of a multiple-field key. (Note
   that I don't consider simply defining a multiple-field key to be unique
   as painful). So ISTM it's much easier to just use surrogate keys and be
   done with it. Only deviate when you have a good reason to do so.
   -- 
   Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
   Pervasive Software      http://pervasive.com    work: 512-231-6117
   vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
   
   

5. Re: Surrogate keys (Was: enums)

   Josh Berkus <josh@agliodbs.com> — 2006-01-19T18:11:51Z

   Jim,
   
    > So ISTM it's much easier to just use surrogate keys and be
   > done with it. Only deviate when you have a good reason to do so.
   
   "The lazy man's guide to SQL database design", but Jim Nasby.
   
   ;-)
   
   --Josh
   
   
   

6. Re: Surrogate keys (Was: enums)

   Mark Mielke <mark@mark.mielke.cc> — 2006-01-19T19:01:14Z

   On Thu, Jan 19, 2006 at 10:11:51AM -0800, Josh Berkus wrote:
   > > So ISTM it's much easier to just use surrogate keys and be
   > >done with it. Only deviate when you have a good reason to do so.
   > "The lazy man's guide to SQL database design", but Jim Nasby.
   > ;-)
   
   Hehe... I was thinking the same thing. I've definately seen cases
   where the use of surrogate keys verges on ridiculous. It hasn't
   harmed the application, except it terms of complexity. It still works.
   It still performs fine. The SQL queries are awful looking. :-)
   
   That's where I would tend to draw the line. For me, I find
   implementation and maintenance to be the most expensive part of my
   applications. My data hasn't yet become large enough to make disk
   space, compute resources, or I/O bandwidth a serious concern.
   
   If I think the use of surrogate keys may make my life harder, I'll try
   not to use them. If I think they may make my life easier, I'll use
   them without blinking an eye. Harder vs. easier = cost to implement.
   
   Cheers,
   mark
   
   -- 
   mark@mielke.cc / markm@ncf.ca / markm@nortel.com     __________________________
   .  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
   |\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   | 
   |  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada
   
     One ring to rule them all, one ring to find them, one ring to bring them all
                          and in the darkness bind them...
   
                              http://mark.mielke.cc/
   
   
   

7. Re: Surrogate keys (Was: enums)

   Jim Nasby <jnasby@pervasive.com> — 2006-01-19T19:48:18Z

   On Thu, Jan 19, 2006 at 02:01:14PM -0500, mark@mark.mielke.cc wrote:
   > On Thu, Jan 19, 2006 at 10:11:51AM -0800, Josh Berkus wrote:
   > > > So ISTM it's much easier to just use surrogate keys and be
   > > >done with it. Only deviate when you have a good reason to do so.
   > > "The lazy man's guide to SQL database design", but Jim Nasby.
   > > ;-)
   > 
   > Hehe... I was thinking the same thing. I've definately seen cases
   > where the use of surrogate keys verges on ridiculous. It hasn't
   > harmed the application, except it terms of complexity. It still works.
   > It still performs fine. The SQL queries are awful looking. :-)
   
   Got an example?
   
   > That's where I would tend to draw the line. For me, I find
   > implementation and maintenance to be the most expensive part of my
   > applications. My data hasn't yet become large enough to make disk
   > space, compute resources, or I/O bandwidth a serious concern.
   
   Which is exactly what my thought process is. If you mix surrogate and
   non-surrogate keys, how do you know which table has which? Sure, while
   you're actively writing the code it's not an issue, but what about 6
   months later? What about if someone else picks up the code?
   
   I know Josh was poking fun with his comment about me being lazy, but
   lazy can make for better code. I can go back to code I wrote 3 years ago
   and I know that 99% of tables will have something_id (where something is
   almost certain to be the table name) as a surrogate key to join on;
   there's no need for me to go and figure out what does and what doesn't
   have a surrogate key. The 1% that don't fall into that generally aren't
   an issue because they're normally very large tables that nothing joins
   to.
   
   There's actually an article floating around somewhere about how lazy
   coders are good coders... :)
   -- 
   Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
   Pervasive Software      http://pervasive.com    work: 512-231-6117
   vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
   
   

8. Re: Surrogate keys (Was: enums)

   Mark Mielke <mark@mark.mielke.cc> — 2006-01-19T20:11:04Z

   On Thu, Jan 19, 2006 at 01:48:18PM -0600, Jim C. Nasby wrote:
   > On Thu, Jan 19, 2006 at 02:01:14PM -0500, mark@mark.mielke.cc wrote:
   > > On Thu, Jan 19, 2006 at 10:11:51AM -0800, Josh Berkus wrote:
   > > > > So ISTM it's much easier to just use surrogate keys and be
   > > > >done with it. Only deviate when you have a good reason to do so.
   > > > "The lazy man's guide to SQL database design", but Jim Nasby.
   > > > ;-)
   > > Hehe... I was thinking the same thing. I've definately seen cases
   > > where the use of surrogate keys verges on ridiculous. It hasn't
   > > harmed the application, except it terms of complexity. It still works.
   > > It still performs fine. The SQL queries are awful looking. :-)
   > Got an example?
   
   Sure, but I have to be vague, because it's a company DB. :-)
   
   The DB has a primary table, that maps the primary key to a surrogate
   key. The surrogate key is used in several 1:1 and 1:N relationships.
   Pretty straight forward. (I tend to put the primary key in the most
   primary table that would have a 1:1 relationship, of which there is
   one in this database - but whatever) The primary key is an identifier
   used for all inputs and outputs to the application. It is used by
   manual and automatic processes internal and external to the company.
   Definately a primary key / surrogate key scenario.
   
   The problem here, is that the primary key *is* a natural key. It
   is generated to be unique, and it is immutable. There are no interfaces
   provided to allow the rename of the key. It is a short character
   string of 5 to 20 characters.
   
   All queries to the table are joined with this primary key/surrogate
   key table, to allow lookup by the primary key, for records only
   identified by the surrogate key.
   
   The database is only likely to have a few thousands records, with
   the 1:N relationships not exceeding 5 or 10, and not recursive.
   For performance, or disk space, it doesn't really matter which way
   they went.
   
   The confusion, though, of joining using a surrogate, that is
   intended to be opaque (the value is never queried), ensures that
   the program has no simple queries. All queries involve at least
   one join.
   
   I said almost ridiculous. It's not enough for me to complain, and
   request a re-design. I don't really care what it does, as long as
   it accepts my data, and allows me to query my data. But, it does
   seem silly to me.
   
   > > That's where I would tend to draw the line. For me, I find
   > > implementation and maintenance to be the most expensive part of my
   > > applications. My data hasn't yet become large enough to make disk
   > > space, compute resources, or I/O bandwidth a serious concern.
   > Which is exactly what my thought process is. If you mix surrogate and
   > non-surrogate keys, how do you know which table has which? Sure, while
   > you're actively writing the code it's not an issue, but what about 6
   > months later? What about if someone else picks up the code?
   
   It's usually pretty obvious, looking at a database diagram. You look
   up the primary key, and see that it only shows up in one table. :-)
   
   > I know Josh was poking fun with his comment about me being lazy, but
   > lazy can make for better code. I can go back to code I wrote 3 years ago
   > and I know that 99% of tables will have something_id (where something is
   > almost certain to be the table name) as a surrogate key to join on;
   > there's no need for me to go and figure out what does and what doesn't
   > have a surrogate key. The 1% that don't fall into that generally aren't
   > an issue because they're normally very large tables that nothing joins
   > to.
   
   I don't disagree with you. I just don't mind deciding to use a surrogate
   key if I'm unsure, and not using a surrogate if it seems more effort than
   gain.
   
   > There's actually an article floating around somewhere about how lazy
   > coders are good coders... :)
   
   Dunno where it started, but that's one of the tenets of the developers
   of Perl. Of course, with Perl 6, they admitted to having made quite a
   few deisgn errors with Perl 5 and earlier... :-)
   
   Cheers,
   mark
   
   -- 
   mark@mielke.cc / markm@ncf.ca / markm@nortel.com     __________________________
   .  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
   |\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   | 
   |  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada
   
     One ring to rule them all, one ring to find them, one ring to bring them all
                          and in the darkness bind them...
   
                              http://mark.mielke.cc/
   
   
   

9. Re: Surrogate keys (Was: enums)

   Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2006-01-20T01:50:17Z

   > Yes. Representation of the DNA is probably best. But - that's a lot of
   > data to use as a key in multiple tables. :-)
   
   No then you have problems with identical twins :)
   
   Chris
   
   
   

10. Re: Surrogate keys (Was: enums)

    Michael Glaesemann <grzm@myrealbox.com> — 2006-01-20T02:41:36Z

    On Jan 20, 2006, at 10:50 , Christopher Kings-Lynne wrote:
    
    >> Yes. Representation of the DNA is probably best. But - that's a  
    >> lot of
    >> data to use as a key in multiple tables. :-)
    >
    > No then you have problems with identical twins :)
    
    And, looking forward, clones.
    
    Michael Glaesemann
    grzm myrealbox com
    
    
    
    
    

11. Re: Surrogate keys (Was: enums)

    Mike Rylander <mrylander@gmail.com> — 2006-01-20T21:34:20Z

    On 1/19/06, Pollard, Mike <mpollard@cincom.com> wrote:
    > Martijn van Oosterhout wrote:
    >
    > > Please provides natural keys for any of the following:
    > >
    > > - A Person
    > > - A phone call: (from,to,date,time,duration) is not enough
    > > - A physical address
    > > - A phone line: (phone numbers arn't unique over time)
    > > - An internet account: (usernames not unique over time either)
    >
    > Ahh, a challenge.  Hmm, not sure about all of them, but here goes:
    >
    > A Person - well, you could use a bit map of their fingerprints, or maybe
    > their retinal scan.  Of course, that could change due to serious injury.
    > Maybe some kind of representation of their DNA?
    
    Unless the person in question happens to be a chimera (yes, they do exist).
    
    ;-)
    
    --
    Mike Rylander
    mrylander@gmail.com
    GPLS -- PINES Development
    Database Developer
    http://open-ils.org
    
    

12. Re: Surrogate keys

    Chris Browne <cbbrowne@acm.org> — 2006-01-21T03:16:26Z

    > On 1/19/06, Pollard, Mike <mpollard@cincom.com> wrote:
    >> Martijn van Oosterhout wrote:
    >>
    >> > Please provides natural keys for any of the following:
    >> >
    >> > - A Person
    >> > - A phone call: (from,to,date,time,duration) is not enough
    >> > - A physical address
    >> > - A phone line: (phone numbers arn't unique over time)
    >> > - An internet account: (usernames not unique over time either)
    >>
    >> Ahh, a challenge.  Hmm, not sure about all of them, but here goes:
    >>
    >> A Person - well, you could use a bit map of their fingerprints, or maybe
    >> their retinal scan.  Of course, that could change due to serious injury.
    >> Maybe some kind of representation of their DNA?
    >
    > Unless the person in question happens to be a chimera (yes, they do exist).
    >
    > ;-)
    
    Yeah, I saw that episode of CSI!  :-)
    -- 
    "cbbrowne","@","gmail.com"
    http://linuxdatabases.info/info/lsf.html
    "Cat's motto: No matter what you've done wrong, always try to make it
    look like the dog did it." -- Unknown
    
    

13. Re: Surrogate keys

    Mike Rylander <mrylander@gmail.com> — 2006-01-21T04:23:15Z

    On 1/21/06, Christopher Browne <cbbrowne@acm.org> wrote:
    > > On 1/19/06, Pollard, Mike <mpollard@cincom.com> wrote:
    > >> Martijn van Oosterhout wrote:
    > >>
    > >> > Please provides natural keys for any of the following:
    > >> >
    > >> > - A Person
    > >> > - A phone call: (from,to,date,time,duration) is not enough
    > >> > - A physical address
    > >> > - A phone line: (phone numbers arn't unique over time)
    > >> > - An internet account: (usernames not unique over time either)
    > >>
    > >> Ahh, a challenge.  Hmm, not sure about all of them, but here goes:
    > >>
    > >> A Person - well, you could use a bit map of their fingerprints, or maybe
    > >> their retinal scan.  Of course, that could change due to serious injury.
    > >> Maybe some kind of representation of their DNA?
    > >
    > > Unless the person in question happens to be a chimera (yes, they do exist).
    > >
    > > ;-)
    >
    > Yeah, I saw that episode of CSI!  :-)
    
    Heh.  I didn't realize they did that already.  I was thinking of the
    show "I Am My Own Twin" from the Discovery Health Channel.
    
    Couldn't find a link from the official page, but:
    http://www.globalspin.com/mt/archives/000547.html .
    
    > --
    > "cbbrowne","@","gmail.com"
    > http://linuxdatabases.info/info/lsf.html
    > "Cat's motto: No matter what you've done wrong, always try to make it
    > look like the dog did it." -- Unknown
    >
    > ---------------------------(end of broadcast)---------------------------
    > TIP 6: explain analyze is your friend
    >
    
    
    --
    Mike Rylander
    mrylander@gmail.com
    GPLS -- PINES Development
    Database Developer
    http://open-ils.org
    
    

14. Re: Surrogate keys (Was: enums)

    Bruno Wolff III <bruno@wolff.to> — 2006-01-24T10:04:45Z

    On Thu, Jan 19, 2006 at 09:53:11 -0500,
      mark@mark.mielke.cc wrote:
    > 
    > Yes. Representation of the DNA is probably best. But - that's a lot of
    > data to use as a key in multiple tables. :-)
    
    On a simple level, this would be a problem for twins.
    There are other complications as well. People are going to have slightly
    different DNA in different cells due to mutations. Though you could probably
    do some averaging over a number of cells to get a single value.
    For people that have had transplants, you could probably define something
    for doing the sample for original material.