Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2010
    Posts
    4

    Unanswered: Best Practices: Two Surrogate Keys

    First time poster on DBForums.com, Hello @ All.


    Would having two surrogate keys be considered bad DB design, for example:

    Table1 (Parent):
    T1_ID (Primary Key)(newsequentialid())
    T1_Object_ID (Secondary Key)(GUID Generated in AppCode before insert)
    Data (varchar(50))

    Table2 (Child):
    T2_ID (Primary Key)(newsequentialid())
    T1_Object_ID_FK (Foreign Key)
    Data (varchar(50))

    Table3 (Child):
    T3_ID (Primary Key)(newsequentialid())
    T1_Object_ID_FK (Foreign Key)
    Data (varchar(50))


    Here is an example of three tables. Table1 is the parent, its primary key is an uniqueidentifer (or GUID) which will be generated on SQL server on every insert, there is also a secondary key which is also a uniqueidentifer but the AppCode will generate this key. This secondary key is what all child tables reference as their foreign key.

    The queries will include both primary and secondary keys depending on the AppCodes needs. I have a highly normalized database, each query requires at least a three way join (some queries are up to fifteen way joins). So by having this secondary key I can update parent records without cascading updates to the child records.

    So my question is, is this a good idea or does this go against DB best practices. If it is doable, what are the pros and cons.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by DBNull View Post
    So by having this secondary key I can update parent records without cascading updates to the child records.
    If you are updating this key then it is not a surrogate.
    Why would you want to update T1_ID?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, that's not accurate. I meant if it is a surrogate there should be no need to update it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, I assumed the SQL Server generated key is the secondary key since you said updates to this would not be cascaded. But you have referred to the AppCode key as the secondary one, but also used it for the foreign keys.

    Is your design correct and accurate?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2010
    Posts
    4
    Yes the T1_Object_ID key will not update to keep the foreign key relationship to all of its child tables. The parent record can change (the row ID but not the secondary key). Does that clear it up?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by DBNull View Post
    Does that clear it up?
    yep, everything except why you think it's necessary to use two keys when one will do

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2010
    Posts
    4
    So I do not have to do cascading updates throughout my 12 tables. The parent record is made "Inactive" and a new parent record is inserted. The secondary key will remain no matter how many parent records are inserted. This my shred some light:

    (I probably should have posted something like this before)

    Parent Table:

    CONFIGURATION_ID uniqueidentifier
    MAS_MODE_LU_ID_FK uniqueidentifier
    COMPUTER_NAME varchar(60)
    COMPUTER_IP_ADDRESS varchar(39)
    IS_LOGIN_SERVER bit
    DATE_ADDED datetime
    ADDED_BY_ID uniqueidentifier
    DATE_CHANGED datetime
    CHANGED_BY_ID uniqueidentifier
    IS_ACTIVE bit
    IS_ARCHIVED bit
    X509CERTIFICATE_BASE64 varchar(MAX)

    Child Table:

    REGISTERED_MODULE_ID uniqueidentifier
    MAS_CONFIGURATION_ID_FK uniqueidentifier
    ASSEMBLY_GUID uniqueidentifier
    ASSEMBLY_VERSION varchar(20)
    ASSEMBLY_SHORT_NAME varchar(60)
    ASSEMBLY_STRONG_NAME varchar(600)
    ASSEMBLY_FILE_NAME varchar(60)
    PARTIAL_SERVICE_ADDRESS varchar(200)
    SERVICE_CONTRACT_TYPE_FULLNAME varchar(600)
    SERVICE_TYPE_FULLNAME varchar(600)
    USERCONTROL_TYPE_FULLNAME varchar(600)
    DISPLAY_NAME varchar(60)
    DATE_ADDED datetime
    ADDED_BY_ID uniqueidentifier
    DATE_CHANGED datetime
    CHANGED_BY_ID uniqueidentifier
    IS_ACTIVE bit
    IS_ARCHIVED bit

    Looking at these two tables you will notice a foreign key relationship in the child table "MAS_CONFIGURATION_ID_FK", so this is a 1:many relationship. You can have many child records keyed to one parent record.

    Lets say I have 500 child records, and I want to update the parent record. Currently our requirements force us to do things similar to this design. Basically we can not truly "delete" anything, we have flags that tell us either the record is active or not ("IS_ACTIVE"). So if we make an update to a parent record, we update the current active record to IS_ACTIVE = false, then insert a new parent record with the data we wanted to change. Since every record has a unique ID I would have to do cascading updates throughout the database.

    Now lets say we change to this table structure:

    Parent Table:

    CONFIGURATION_ID uniqueidentifier
    CONFIGURATION_REAL_ID uniqueidentifier <-- NEW COLUMN (Secondary Key)
    MAS_MODE_LU_ID_FK uniqueidentifier
    COMPUTER_NAME varchar(60)
    COMPUTER_IP_ADDRESS varchar(39)
    IS_LOGIN_SERVER bit
    DATE_ADDED datetime
    ADDED_BY_ID uniqueidentifier
    DATE_CHANGED datetime
    CHANGED_BY_ID uniqueidentifier
    IS_ACTIVE bit
    IS_ARCHIVED bit
    X509CERTIFICATE_BASE64 varchar(MAX)

    Child Table:

    REGISTERED_MODULE_ID uniqueidentifier
    MAS_CONFIGURATION_REAL_ID_FK uniqueidentifier <-- Foreign Key to Secondary Key in parent table
    ASSEMBLY_GUID uniqueidentifier
    ASSEMBLY_VERSION varchar(20)
    ASSEMBLY_SHORT_NAME varchar(60)
    ASSEMBLY_STRONG_NAME varchar(600)
    ASSEMBLY_FILE_NAME varchar(60)
    PARTIAL_SERVICE_ADDRESS varchar(200)
    SERVICE_CONTRACT_TYPE_FULLNAME varchar(600)
    SERVICE_TYPE_FULLNAME varchar(600)
    USERCONTROL_TYPE_FULLNAME varchar(600)
    DISPLAY_NAME varchar(60)
    DATE_ADDED datetime
    ADDED_BY_ID uniqueidentifier
    DATE_CHANGED datetime
    CHANGED_BY_ID uniqueidentifier
    IS_ACTIVE bit
    IS_ARCHIVED bit

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    could you not accomplish this with a history or auditing table maintained by triggers or maybe even perhaps Change Data Capture provided SQL 2008. this design sounds disastrous like everybody else. Is this HIPPA compliance?

    Keeping all of the inactive parent records in the table complicates your design, and it does not sound very maintainable. It sounds like you will bloat your parent table with history data which will cause you a number of issues trying to get usable information back out.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Feb 2010
    Posts
    4
    Well I know what you saying but, architecture of the database is in place for a reason but its to in depth to really say why. Just assume this is what I have to work with. The question is. From a DB best practice stand point, is there anything wrong with having two row level keys, one as its primary ID, and another key that child records foreign key to, what are the pros and cons of doing this.

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Foreign key must reference a PK or a unique constraint in the parent. Since your secondary "key" is not a key at all, because it will contain non-unique values, you will have to deal with that as well.
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you asked about best practices. well best practices would be the 2 other approaches I mentioned. this is ugly and difficult to maintain and complicated to implement. all of that does not equal best practices. good luck.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by DBNull View Post
    Well I know what you saying but, architecture of the database is in place for a reason but its to in depth to really say why.
    You are already talking about modifying the architecture if you are considering adding a secondary surrogate key.
    You asked for opinions, and the general consensus is that what you have is a bad design, and what you are planning to do is also a bad design.
    If it's not practically useful, then it's practically useless.

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

  13. #13
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by DBNull View Post
    First time poster on DBForums.com, Hello @ All.
    So by having this secondary key I can update parent records without cascading updates to the child records.
    I am not clear on this design and the need to have many parent records with a current status to distinguish historical rows. Why would you need to do cascading updates/deletes on the child record if the parent changed? Isn't the relationship still coherent without the necessity of creating new parent records? If the parent changed does this mean the relationship with child records is no longer valid?

Posting Permissions

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