Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80

    Surrogate Keys - What Design Phase?

    The Data Model Survey

    A definition of terms. Note that all of the following are Data Models.

    [list=1][*]An Entity Relationship Diagram (ERD) only shows entities and relationships between entities. It is the simplest logical model.[*]A Key-Based Model shows the entities, their primary keys, alternate keys, foreign keys and the relationships between entities. It is the intermediate logical model.[*]A Fully Attributed Model shows entities, keys, non-key attributes and the relationships between entities. It is the final logical model.[*]A Physical Model converts entities to tables, attributes to columns and applies the constraints of the host RDBMS to the Logical Model.[*]The Database Schema is the implementation of the Physical Model on the host RDBMS.[/list=1]

    I hope the survey can guide our discussion somewhat.
    my understanding is that surrogate keys are assigned only in step 4, physical model
    could be wrong, though (wouldn't be the first time)
    note surrogate key is not the same thing as alternate key

    rudy
    Is that to ensure that all of the attributes required to uniquely identify a tuple are present? Why can't surrogate keys be assigned at this point?
    Bradley

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Surrogate Keys - What Design Phase?

    Originally posted by bcrockett
    Is that to ensure that all of the attributes required to uniquely identify a tuple are present? Why can't surrogate keys be assigned at this point?
    What would be the point of a surrogate prior to step 4?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    surrogate keys are not a logical modelling consideration

    you might as well partition the tables across RAID drives in logical modelling phase too

    entities and relationships = logical

    primary and foreign keys = logical

    a surrogate key (typically an autoincrement numeric column) is used solely for the purpose of improving efficiency in joins between related tables

    also sometimes to reassure DBAs afraid of primary keys that might change values

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

  4. #4
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    Originally posted by r937

    also sometimes to reassure DBAs afraid of primary keys that might change values

    That's what I was thinking of. My thought was that we should plan for the possibility of primary keys changing values, and use surrogate key(s) with unique constraints on the primary key attribute(s) if they could possibly change.
    Bradley

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    but primary keys do change

    what's wrong with that -- LOGICALLY???

    i mean, physically, yeah, i can understand why a DBA might be concerned, wouldn't want triggers firing off to update child rows, might lock the app for a second or two...

    but that's PHYSICAL

    note: i am not going to discuss/argue surrogate keys versus natural keys -- there are pros and cons to both

    all i'm doing is pointing out that in the LOGICAL model stage, which was the original question here, all you have is primary keys, and so what if they change?

    and by the way, if they do change, that's what ON UPDATE CASCADE is for!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Surrogate Keys can be left for the physical model phase 4.

    During the key based modeling phase 2 surrogates can be introduced if you want. Some advocates of surrogates recommend making all primary keys for all entities surrogate keys from the start. There are advantages to this as well as risks. It can alleviate you of composite keys, but it can also make certain relationships untenable in large models and hide the composition of the natural keys.

    I like surrogates first. I use them alot and I have been happy with the results.

    This is a sensitive subject.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    surrogate versus natural, yes, that is a sensitive subject

    surrogates in logical design should not be sensitive, because surrogates are a physical design consideration, end of story

    next you're going to claim that unique constraints (i.e. a business rule) aren't logical either!



    (please note the smiley)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710

  9. #9
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    The draw back of using natural keys in the logical model and introducing comprehensive surrogate primary keys in the physical model is the effort required to rewire all the relationships from the natural to surrogate keys. If you have more than one hundred tables its like swallowing a lump of oatmeal.


  10. #10
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    I happen to like lumpy oatmeal...



    ...but I certainly don't like having to restate the primary keys when going from step 3 to 4 (or even from 2 to 3) - especially when the only natural keys are composite.
    Bradley

  11. #11
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    I suggest this link

    http://r937.com/20020620.html

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hey, thanks for mentioning that -- it's 18 months old, already

    yeah, my favourite from that builder.com.com (hey, don't blame me, that's their domain name) article is this --

    Inner/Outer Join
    Inner Joins are joined on the inner columns of two tables. Outer Joins are joined on the outer columns of two tables.



    what a laff riot, eh?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    This is an article contained in the link above that is very interesting:

    http://www.dbpd.com/vault/9805xtra.htm

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thank you, thank you, i'll be here all week, try the chicken and don't forget to tip your waitress

    see also http://r937.com/sqllinks.cfm -- several surrogate versus natural links in there


    rudy

  15. #15
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    OK - so surrogate keys have their place. At what stage should they be introduced into the logical model? Why not at stage 2?
    Bradley

Posting Permissions

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