What to do when your natural key is broken.

  • I have a data modeler who is set on developing using a natural key. The issue at hand is this,

    1) The natural key which they intend to use will be broken immediately in our system because of what it represents.

    2) The natural key models a type of facility but only 1 facility of the same type is modeled by this key.

    3) Our footprint allows for in several cases for multiple facilities of the same type to exist and thus have the same natural key apply.

    4) The system I am working on is for the purpose of modeling all facilities.

    5) The data modelers issue is that other systems wanting to report from us will be unable to manage the fact the "natural key" exists more than once and will thus cause invalid information.

    Now as for the answer I have mine and have posted in the past what I would do. But in this case I am too close to the issue for others to seem to take me as seriously not focused on being stubborn as they see it. So I am posting here for comments and suggestions to see if my solution is the concensus or if maybe someone else sees another way I haven't thought of that will satisfy the data modeler.

    Any and all input is appreciated.

  • Hmm... sounds to me that you have a much bigger issue than a design disagreement.

    A natural key that will end up with duplicates is no longer a natural key. If uniqueness can't be guaranteed, then it's not a natural key. I don't really care whether it's "neat", "clean", insert your own adjective here....not unique = PROBLEM. Your only options as of right now are to a. start over and find another set of fields that TRULY constitute a natural key. If the entity you're dealing with doesn't have such a set, then it might be time to pull in an arbitrary unique identifier (some form of a surrogate key).

    The bigger question now is - you both seem to "see" the problem, and yet he doesn't seem to want to do anything about it. Why? What's so important that he wants to keep pursuing something broken?

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Matt Miller (6/26/2008)


    Hmm... sounds to me that you have a much bigger issue than a design disagreement.

    A natural key that will end up with duplicates is no longer a natural key. If uniqueness can't be guaranteed, then it's not a natural key. I don't really care whether it's "neat", "clean", insert your own adjective here....not unique = PROBLEM. Your only options as of right now are to a. start over and find another set of fields that TRULY constitute a natural key. If the entity you're dealing with doesn't have such a set, then it might be time to pull in an arbitrary unique identifier (some form of a surrogate key).

    The bigger question now is - you both seem to "see" the problem, and yet he doesn't seem to want to do anything about it. Why? What's so important that he wants to keep pursuing something broken?

    Matt is spot on with most of this... the only thing he left out is that you must take the data modeler out for a nice porkchop dinner where you can emphasize the problem properly... 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply