aspose file tools*
The moose likes OO, Patterns, UML and Refactoring and the fly likes Database Surrogate Keys - Visible? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Engineering » OO, Patterns, UML and Refactoring
Bookmark "Database Surrogate Keys - Visible?" Watch "Database Surrogate Keys - Visible?" New topic
Author

Database Surrogate Keys - Visible?

Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
I'm working with a database with surrogate keys all around. I tried to make a REST-ish interface where every resource has a simple URL, and used the keys in the URL, e.g. //server/Asset/123 Googling about on surrogate keys I find some division of opinion on making surrogate keys visible.

Guru Joe Celko calls a visible key a Exposed Locator and suggests "they are handy for lazy, non-RDBMS programmers who do not want to research or think! This is the worst way to program in SQL."

Wikipedia attributes the term surrogates to Hall, Owlet and Todd, who happily allow them to be visible. The article uses visible keys "largely because it is more data model rather than storage model oriented."

Waddya think? Truly evil? Ok in a URL? What other values would be good in Cool URIs? Database forum?
[ October 22, 2007: Message edited by: Stan James ]

A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Peer Reynders
Bartender

Joined: Aug 19, 2005
Posts: 2906
Without more information about your domain I would lean towards:
  • Personally I'm a big fan of synthetic/surrogate keys as there is no need to change them even if the domain changes. However I also believe the the system should keep those keys away from the user (unless it is for temporary diagnostic purposes). I would be loath to give up the freedom to have resource 12345 be resource 987654321 tomorrow in case I have a good reason to.
  • At the same time I also believe that you should always identify one or more business keys (even if it is something as mundane as a unique name) that make sense to the users and domain experts and use those in the human-oriented and external interfaces. If I remember correctly Richardson and Ruby always used business keys in their URIs in RESTful Web Services (amazon US).



  • With this approach you maintain redundant identification:
  • You may never have to change your synthetic keys but you can change them on a whim if you have to.
  • You can change the business keys and adapt the human-oriented and external interfaces without having to re-write the internals.
  • You can change the (redundant) mapping anytime as long as one key system remains stable while the other one is changing.


  • However there are always exceptions and I don't know if you can justifiably invoke one.


    "Don't succumb to the false authority of a tool or model. There is no substitute for thinking."
    Andy Hunt, Pragmatic Thinking & Learning: Refactor Your Wetware p.41
    Stan James
    (instanceof Sidekick)
    Ranch Hand

    Joined: Jan 29, 2003
    Posts: 8791
    Thanks, that makes perfect sense. Of course we don't have a unique non-volatile business key handy in many tables. Rats. I'll have to see how much of a stretch it would be to invent them. It might wind up like AOL ... "That name is already in use. How about NewObject106358?"

    I left out the bit where the data folks would like to be able to drop the database and reload from other sources, generating new surrogate keys as they go. That's NAND with using them in my URIs.
    Peer Reynders
    Bartender

    Joined: Aug 19, 2005
    Posts: 2906
    Originally posted by Stan James:
    Of course we don't have a unique non-volatile business key handy in many tables. Rats. I'll have to see how much of a stretch it would be to invent them. It might wind up like AOL ... "That name is already in use. How about NewObject106358?".


    You may be justified to only name your aggregates:
    An AGGREGATE is a cluster of associated objects that we treat as a unit for the purpose of data changes.
    Each AGGREGATE has a root and a boundary. The boundary defines what is inside the AGGREGATE. The root is a single, specific ENTITY contained in the AGGREGATE. (DDD Eric Evans)

    (replace "object" with "resource").

    REST may be big on every resource having its own URI but it is also about hypermedia. As long as you create a resource representation for the aggregate root that lists its parts you could always declare that sub-ordinated resources have to be located through the aggregate root and cannot be addressed directly.
    Scott Ambler
    author
    Ranch Hand

    Joined: Dec 12, 2003
    Posts: 608
    As soon as you make a surrogate key visible to your users you've effectively given it business meaning, turing it into a natural key. For example, social security number (SSN) is a surrogate key which evolved into a natural key decades ago.

    I've got some advice around keys at http://www.agiledata.org/essays/keys.html that may help.

    - Scott


    <a href="http://www-306.ibm.com/software/rational/bios/ambler.html" target="_blank" rel="nofollow">Scott W. Ambler</a><br />Practice Leader Agile Development, IBM Rational<br /> <br />Now available: <a href="http://www.ambysoft.com/books/refactoringDatabases.html" target="_blank" rel="nofollow">Refactoring Databases: Evolutionary Database Design</a>
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Database Surrogate Keys - Visible?
     
    Similar Threads
    two forign keys
    Two PKs
    Regarding PK
    MySQL resetting auto-increment
    difference between categorybag and identifier bag in UDDI