Professional Web Applications Themes

Best practices for primary key (GUID VS Identity) - Microsoft SQL / MS SQL Server

"> This is obviously your first large database project, and you have never > had course in data modeling. You have set up a False Dichotomy. A very > false one, in fact." A little too wound up there or too arrogant?? I am not a DBA. Thank g-d. This is not the kind of help and direction I was looking for. If someone posted a question on web development asking something that I may consider simple, would I insult them and tell them to go and take courses and remind them how I normally would have to save them ...

  1. #1

    Default Re: Best practices for primary key (GUID VS Identity)

    "> This is obviously your first large database project, and you have never
    > had course in data modeling. You have set up a False Dichotomy. A very
    > false one, in fact."
    A little too wound up there or too arrogant??

    I am not a DBA. Thank g-d. This is not the kind of help and direction I was
    looking for.

    If someone posted a question on web development asking something that I may
    consider simple, would I insult them and tell them to go and take courses
    and remind them how I normally would have to save them later??

    No obviously not.



    "Joe Celko" <anonymousdevdex.com> wrote in message
    news:OsSB21sJDHA.1612TK2MSFTNGP11.phx.gbl...
    > >> I am about to start designing schema for a large database. I am
    > trying to weigh up the pros and cons for using either a GUID or auto
    > incrementing IDENTITY column for the primary keys. <<
    >
    > This is obviously your first large database project, and you have never
    > had course in data modeling. You have set up a False Dichotomy. A very
    > false one, in fact.
    >
    > Neither a GUID or IDENTITY can be a key **by definition**; they are
    > attributes of the PHYSICAL storage and have nothing to do with the
    > logical model. You are starting on the physical implementation without
    > a logical model. And you're already trapping yourself in one of two bad
    > decisions that will destroy the dat integrity of the project.
    >
    > The right questions to ask at the start of a project are:
    >
    > 1) Can I buy a package? How much does it cost?
    > 2) Has anyone done this before? Who?
    > 3) What are the industry standards for this application?
    > 4) What are the entities?
    > 5) What are the relationships?
    > 6) What are the business rules?
    >
    > Have you learned ORM yet? Might be the best place to start.
    >
    > I made a living fixing database projects that get ed up by things
    > like this. I usually come in after the first year. That is a "magic
    > number" because it is just enough time fot the database to get large,
    > have been used enough to be important and to have someone do an annual
    > report off of it. That is when the owners see that everything is crap
    > and that they need to clean it up.
    >
    > --CELKO--
    > ===========================
    > Please post DDL, so that people do not have to guess what the keys,
    > constraints, Declarative Referential Integrity, datatypes, etc. in your
    > schema are.
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    SamIAm Guest

  2. #2

    Default Re: Best practices for primary key (GUID VS Identity)

    Why can't an Identity be a Key? If I want a customer ID say and I want that
    to be an integer that is unique, WTF is wrong with using the system
    allocated Identity value? Same goes for a GUID, if I want to whack a unique
    stamp on something that isn't necessarily unique based on it's other
    properties then it does the job.

    Anyway, I ain't a DBA either, but here's my 2 cents. The Identity takes less
    storage and will match quicker in joins (int versus string). The existence
    of Identities in your table may impact update performance as the next
    Identity requires knowledge of the current table data. A GUID on the other
    hand is randomly generated, though if you make it a unique key (PK) then the
    usual performance impact of a unique constraint will still come into to play
    and more so with a GUID as again it's a case of int versus string. Oh, and
    yeah, what are the business rules? ;-)



    "SamIAm" <samiamrubbachicken.com> wrote in message
    news:OfF2HeDQDHA.4024tk2msftngp13.phx.gbl...
    > "> This is obviously your first large database project, and you have never
    > > had course in data modeling. You have set up a False Dichotomy. A very
    > > false one, in fact."
    >
    > A little too wound up there or too arrogant??
    >
    > I am not a DBA. Thank g-d. This is not the kind of help and direction I
    was
    > looking for.
    >
    > If someone posted a question on web development asking something that I
    may
    > consider simple, would I insult them and tell them to go and take courses
    > and remind them how I normally would have to save them later??
    >
    > No obviously not.
    >
    >
    >
    > "Joe Celko" <anonymousdevdex.com> wrote in message
    > news:OsSB21sJDHA.1612TK2MSFTNGP11.phx.gbl...
    > > >> I am about to start designing schema for a large database. I am
    > > trying to weigh up the pros and cons for using either a GUID or auto
    > > incrementing IDENTITY column for the primary keys. <<
    > >
    > > This is obviously your first large database project, and you have never
    > > had course in data modeling. You have set up a False Dichotomy. A very
    > > false one, in fact.
    > >
    > > Neither a GUID or IDENTITY can be a key **by definition**; they are
    > > attributes of the PHYSICAL storage and have nothing to do with the
    > > logical model. You are starting on the physical implementation without
    > > a logical model. And you're already trapping yourself in one of two bad
    > > decisions that will destroy the dat integrity of the project.
    > >
    > > The right questions to ask at the start of a project are:
    > >
    > > 1) Can I buy a package? How much does it cost?
    > > 2) Has anyone done this before? Who?
    > > 3) What are the industry standards for this application?
    > > 4) What are the entities?
    > > 5) What are the relationships?
    > > 6) What are the business rules?
    > >
    > > Have you learned ORM yet? Might be the best place to start.
    > >
    > > I made a living fixing database projects that get ed up by things
    > > like this. I usually come in after the first year. That is a "magic
    > > number" because it is just enough time fot the database to get large,
    > > have been used enough to be important and to have someone do an annual
    > > report off of it. That is when the owners see that everything is crap
    > > and that they need to clean it up.
    > >
    > > --CELKO--
    > > ===========================
    > > Please post DDL, so that people do not have to guess what the keys,
    > > constraints, Declarative Referential Integrity, datatypes, etc. in your
    > > schema are.
    > >
    > > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > > Don't just participate in USENET...get rewarded for it!
    >
    >

    Dmand Guest

  3. #3

    Default Re: Best practices for primary key (GUID VS Identity)

    > Why can't an Identity be a Key? If I want a customer ID say and I want
    that

    An IDENTITY can't be a *natural* key because it is not "a subset of the
    attributes which uniquely identify the entity" i.e. a Primary Key. IDENTITY
    can be used as an artificial, surrogate key and the pros and cons of doing
    so have often been repeated in this group.

    However, if you do use identity as a key you MUST also declare the true,
    natural key of the table as non-NULL and UNIQUE otherwise you have no
    natural key and therefore no uniqueness and no RI.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  4. #4

    Default Re: Best practices for primary key (GUID VS Identity)

    > Why can't an Identity be a Key? If I want a customer ID say and I want
    that
    > to be an integer that is unique, WTF is wrong with using the system
    > allocated Identity value? Same goes for a GUID, if I want to whack a
    unique
    > stamp on something that isn't necessarily unique based on it's other
    > properties then it does the job.
    Using either an identity or a guid is easier in pretty much every way. The
    problem with making things easier for yourself is that you look more like a
    beginner and this is frowned upon by the 'experts'.

    --
    Michael Culley


    Michael Culley Guest

Similar Threads

  1. Primary Scratch & Windows Primary Paging file?
    By Tommy Oberst in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 14
    Last Post: April 15th, 10:26 PM
  2. GUID and CFProcParam
    By DeliK in forum Coldfusion Database Access
    Replies: 5
    Last Post: June 23rd, 11:03 PM
  3. GUID question
    By Rob in forum ASP.NET Security
    Replies: 3
    Last Post: January 22nd, 07:28 PM
  4. Retrieving GUID from DB2 UDB
    By Knut Stolze in forum IBM DB2
    Replies: 0
    Last Post: August 6th, 07:55 AM
  5. GUID as a parameter
    By in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 1st, 09:43 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139