Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: use of Enterprise ID's

    Hi,

    I have switched from natural keys to surrogate keys a few years ago, and I wouldn't dream of going back.

    I've read an article that promotes the use of Enterprise ID's (EID). A surrogate key that is not only unique within 1 table, but unique over all the tables in your database(s) and beyond.

    Has anyone done this? What are your impressions so far?

    What are the implications when developing software that works on top of it?

    I'm especially thinking of the consequences when using those EID's in reference tables during the development phase. Applications can use those (E)ID's to define differnt execution paths (if ID_CO_TYPE_CUSTOMER = 1 then ... else ...)
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    GUIDs are an excellent example of an Enterprise ID. Within a given Active Directory Tree, GUID values are unique. This means that any machine (database, app, web, client, whatever) can generate an ID with confidence that it will be unique within that server's "known universe" of machines. GUIDs are both "one size fits all" and larger than most other ID values, but the benefits far outweigh those issues for me. A nice side effect of the size and construction of GUID values is that I think that users should use natural keys, and that surrogate keys should never be directly used by the user (displayed by the app or typed in by the user) and GUID values effectively discourage that use.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I use surrogate keys almost exclusively, and GUIDs most of the time. I've never came across a situation where I regretted using GUIDs, but be aware that there can be some performance side effects that crop up if the table gets really huge. For an absolutely massive implementation, I'd favor ints.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by Wim
    I have switched from natural keys to surrogate keys a few years ago, and I wouldn't dream of going back.
    Really? I use both. It's plain wrong to set this up as if you have to make a choice.

    If I have an Invoice table where business rules require InvoiceNum to be unique then InvoiceNum will be a key. If I add a surrogate key called InvoiceID then InvoiceNum will still be a key as well. Why would anyone want to limit themselves always to just one or the other? Do you really tell business users that you cannot enforce business rules?

    Database modelling methods that are based on semantics and business rules always require that natural keys be identified and used (I prefer the term "business key" rather than "natural key"). That includes 5NF, ORM and even Kimball!

    Quote Originally Posted by Wim
    I've read an article that promotes the use of Enterprise ID's (EID). A surrogate key that is not only unique within 1 table, but unique over all the tables in your database(s) and beyond.
    In fact that's what Codd proposed 30 years ago in his RM/T paper. An important problem is then how to identify where the same fact is created in more than one place with different surrogate keys. Only the semantics of the natural (non-surrogate) attributes make that possible. That's why the natural key is still essential if your data model is to represent the real world accurately.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dave, i was totally with you, you were doing great, right up until your last paragraph

    then you totally lost me in jargon

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

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Actually, his last paragraph made perfect sense to me. Sometimes the same "fact" can be create in two different places. The name of a client handled by two different sales reps, for instance.
    In such a case, you would not want them represented by different records with different GUIDs. So either use a natural key, or have a process in place for merging records.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I keep bookkeeping columns in each table: USR_CREATE, TS_CREATE, USR_MAINT, TS_MAINT, NR_MAINT (= number of times the record was altered), after creation USR_CREATE = USR_MAINT, TS_CREATE = TS_MAINT and NR_MAINT = 1. These columns are seldom used, but are invaluable for "forensic" investigations.

    I am playing with the thought of creating a supertype table, lets call it SUPER, with just those columns and the ID (PK). Then let all tables TABLE_X have an ID that refers to (FK) SUPER. With triggers taking care of creation and updating those bookkeeping fields in SUPER and reusing the value of the newly created SUPER.ID in the TABLE_X.ID .

    Reason: those bookkeeping columns are seldom used, so not needed in everyday queries. And I would have a means to assert that all ID's over all tables would be unique, because I had GUIDs removed from my list of valid PK candidates after reading some articles dissuading its use. Now I read you guys are actively using GUIDs and promoting them, so I took them back in consideration. Guess for each proponent of whatever one can easily find a proponent of the opposite ...

    Was wondering how such a SUPER table would hold in true production environments. Huge after a while, bottleneck with all those triggers, ...? Is it any good idea?
    Last edited by Wim; 02-05-09 at 07:21.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    What problem are you actually trying to solve? Why do you care about "asserting that all ID's over all tables would be unique"?

    For audit purposes consider using Change Tracking or Change Data Capture (SQL Server 2008 features). Triggers in SQL Server don't really make a good foundation for audit and they can be complex for developers and DBAs to support. I almost never recommend triggers for updating or changing data - only for enforcing business rules.

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What problem are you actually trying to solve? Why do you care about "asserting that all ID's over all tables would be unique"?
    The idea behind an EID is that it is unique not only within the table it is used in, but unique over all the tables.

    The advantage of using an EID, is that it is easier to refactor your database in time.
    I have inherited a database where different types of customers are stored in different tables. I figured out it would be better to make supertype/subtypes of them.
    Putting all the common data in the supertype table (avoiding redundancy).

    But the cost of this undertaking would be so huge (updating PK's where they would overlap, updating FK's to those altered PK's) ... I'll just have to keep writing reports to flag those differences between the denormalised columns. Making reports with the current database structure is also hard when they want an overview of all their customers ... UNION ALL .... UNION ALL ... UNION ALL ...

    The reason for this post was to hear from real-life experiences if using EIDs is worth the trouble.
    Last edited by Wim; 02-06-09 at 11:40.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In my experience, they usually are.
    Unless your database is going to be huge, I would have no problem using them.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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