Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51

    Question Unanswered: 4 key primary key vs 1 key 'artificial' primary key

    Hi all

    I have the following table

    CREATE TABLE [dbo].[property_instance] (
    [property_instance_id] [int] IDENTITY (1, 1) NOT NULL ,
    [application_id] [int] NOT NULL ,
    [owner_id] [nvarchar] (100) NOT NULL ,
    [property_id] [int] NOT NULL ,
    [owner_type_id] [int] NOT NULL ,
    [property_value] [ntext] NOT NULL ,
    [date_created] [datetime] NOT NULL ,
    [date_modified] [datetime] NULL
    )

    I have created an 'artificial' primary key, property_instance_id. The 'true' primary key is application_id, owner_id, property_id and owner_type_id

    In this specific instance
    - property_instance_id will never be a foreign key into another table
    - queries will generally use application_id, owner_id, property_id and owner_type_id in the WHERE clause when searching for a particular row
    - Once inserted, none of the application_id, owner_id, property_id or owner_type_id columns will ever be modified

    I generally like to create artificial primary keys whenever the primary key would otherwise consist of more than 2 columns.

    What do people think the advantages and disadvantages of each technique are? Do you recommend I go with the existing model, or should I remove the artificial primary key column and just go with a 4 column primary key for this table?

    Thanks Matt

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no the subject of surrogate and natural keys, we already have many threads -- not sure if that will work, it's a search for surrogate and natural

    one thing troubles me, and that is how you have both owner_id and owner_type_id in your candidate key

    doesn't that violate some normal form or another? isn't owner_type_id totall dependent on which owner it is?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51
    Originally posted by r937
    no the subject of surrogate and natural keys, we already have many threads -- not sure if that will work, it's a search for surrogate and natural

    one thing troubles me, and that is how you have both owner_id and owner_type_id in your candidate key

    doesn't that violate some normal form or another? isn't owner_type_id totall dependent on which owner it is?
    Thanks for the search keyword tips. I did indeed find a number of helpful threads.

    Matt

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I believe the general resolution was that Plain-Bellied Sneetches prefer natural keys, while Star-Bellied Sneetches see the advantages of surrogate keys.

    Experts opinions varried:
    Moe: Surrogate Keys
    Larry: Natural Keys
    Curly: Nyuck nyuck nyuck

    We are still awaiting final Papal Dispensation on the subject.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hilarious

    i'm afraid that rome will tell you that it can only be a natural primary key, as surrogates are the work of the devil

    one of curly's best lines is "i'm tryna think but nothin happens!" which is how i feel sometimes when asked to list the pros and cons of surrogate keys versus natural

    maybe i should just say the words "surrogate key" again, and perhaps also the words "natural key" -- so it shouldn't be a total loss, that way they will be salted quite heavily in this post, and therefore this thread, for the benefit of search engines


    moe and larry: "niagara falls!! slowly i turn... step by step... inch by inch..."
    [moe and larry advance on curly, who backs up into the bathroom, and falls into the bathtub]
    moe: "what are you doing in there?!"
    curly: "so it shouldn't be a total loss, i'm takin a bath"
    moe: "a bath?!"
    curly: "yeah, a bath"
    moe (after a slight pause): "move over"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2003
    Posts
    94
    For what it's worth:

    it depends upon the scale of the data, the query composition and whether or not you need to sub index on a primary's sub key.

    given

    application_id, owner_id, and property_id

    being the key, the whole key and nothing but the key, do you ever need to index to owner_id alone? if yes then:

    owner_id, application_id, and property_id

    and so on.

    Do you join across tables, i.e. owner_id -> owner details and property_id -> property details? If so, which ordering of the composite key gives fastest access? Who the hell knows without knowledge of the data definition? Nobody, that's who.

    There's no generic solution to this question, it depends entirely on your data, and you should normalize to a correctly third normal multiple key and then procedurally denormalize to a compound key if your unique scenario requires it.

Posting Permissions

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