Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Posts
    4

    Revisited Lookup Lists and Surrogate Key vs. Natural Key

    Ok I back with Lookup Lists and Surrogate Key vs. Natural Key, this time I have a complex scenario.

    We have 3 dropdown lists on a screen; they represent a medical condition for instance a heart attack:

    Underlying Cause
    Treatment
    Initial Outcome

    Underlying Cause would be a list of all possible conditions that could cause the heart attack.

    Treatment items would depend on Underlying Cause and would be an empty list until a selection is made in Underlying Cause which would fire off the list being populated.

    Initial Outcome would also be dependent on the Treatment.

    Also this screen would be installed at many different sites and each site may have different descriptions for the items in the lists although they would have the same meaning, they may even be in a different language.

    So I couldn't rely on the descriptions to code the logic. I would use a surrogate key to define the logic.

    When I save the record should I store the surrogate key or the description?

    My thoughts are I should store the surrogate key.

    I would like yours thoughts on this, particularly from the Natural Key advocates and how they would address this scenario.

    Thanks

    Richard

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dexon123@hotmail.
    When I save the record should I store the surrogate key or the description?
    store the surrogate key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by dexon123@hotmail.
    Ok I back with Lookup Lists and Surrogate Key vs. Natural Key, this time I have a complex scenario.
    You were gone?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by dexon123@hotmail.
    Ok I back with Lookup Lists and Surrogate Key vs. Natural Key, this time I have a complex scenario.

    We have 3 dropdown lists on a screen; they represent a medical condition for instance a heart attack:

    Underlying Cause
    Treatment
    Initial Outcome

    Underlying Cause would be a list of all possible conditions that could cause the heart attack.

    Treatment items would depend on Underlying Cause and would be an empty list until a selection is made in Underlying Cause which would fire off the list being populated.

    Initial Outcome would also be dependent on the Treatment.

    Also this screen would be installed at many different sites and each site may have different descriptions for the items in the lists although they would have the same meaning, they may even be in a different language.

    So I couldn't rely on the descriptions to code the logic. I would use a surrogate key to define the logic.

    When I save the record should I store the surrogate key or the description?

    My thoughts are I should store the surrogate key.

    I would like yours thoughts on this, particularly from the Natural Key advocates and how they would address this scenario.

    Thanks

    Richard

    In healthcare systems I have worked with there are organisational and national standards that mandate standard diagnosis, treatment codes and so forth. There are also international standards for those things (ICD, SNOMED).

    I'm very surprised if your organisation isn't already using such standards. If they are then they already have a suitable key - the diagnosis or treatment codes specified in the standard.

  5. #5
    Join Date
    Jan 2009
    Posts
    4
    Quote Originally Posted by dportas
    In healthcare systems I have worked with there are organisational and national standards that mandate standard diagnosis, treatment codes and so forth. There are also international standards for those things (ICD, SNOMED).

    I'm very surprised if your organisation isn't already using such standards. If they are then they already have a suitable key - the diagnosis or treatment codes specified in the standard.
    Yes we do use those codes. We also use other codes. We have to record items that we expect to become coded items in the future. Also some of the systems we have to interface to have multiple definition's of national codes (ethnic origin is one) so using a national code would be in-practical. We generally have our own internal keys and have corresponding entries in the look-up table to national and local definitions.

    The problem i have is we have a new director advocating using natural keys he does not have a healthcare background. So far i am convinced a surrogate key is the best method for our business model. I am interested to see if natural keys could provide a viable solution.

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I still don't see any problem with using an official code. You can choose the most useful or comprehensive of the standards and stick to those codes throughout for your foreign keys.

    You can still hold the mapping to the other alternative coding schemes in separate tables. You only have to store that once and presumably you would have to do the same if you used a surrogate anyway.

    Why do that rather than use a surrogate? Well, you've already suggested one reason why. You need to create codes for things that don't yet exist in the standard list. That means you'll need new lists of codes that the users can understand and enter into the system (a surrogate alone won't do of course because users never see it). You'll want a central authority to be responsible for creating those codes and they will use some kind of coding scheme to make sure they don't use the same code twice.

    The modern buzzword for this process is Master Data Management (we used to call it just plain Data Management!). In other words you will need more than just a surrogate - you need a code with a single meaning defined by some central authority and understood by all your users, AKA a business key or natural key.

Posting Permissions

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