DBAs are evil
A long time ago just down the road, no matter who asked you your name, you always gave the same reply.
" I am Connor MacLeod of the Clan MacLeod."
DBAs, who where good at the time, where stoked. FirstName, LastName and Clan.. Nice natural composite key.
As usual, we screwed it up, breed like rabbits and ended up doing Satan's
work. The answer now depend on who's asking…
To the telephone company I am SUCKER093546
To the
Government I am SLAVE8273
And who is responsible for this? DBAs. They don't see people as normal people do, they are just nodes on a B-tree and the B-Tree damn well better be as compact as possible.
And so it came for my time in the devil's recruitment drive. Should the identifier be an INT > 0 with the Identity property set or a GUID?
| Identity | GUID | Weighting |
| Smallest | Largest | 10 |
| Not Updateable | Updateable | 10 |
| Fastest | Slowest | 10 |
| Locally unique | Globally Unique | 50 |
| Proprietary | Open Implementation | 10 |
| Hardest maintenance | Easiest maintenance | 10 |
The highest weighting is given to the scope of the uniqueness for this application. It is really the ability of a client to create a valid key regardless of connectivity state. This is core functionality so a no-brainer really.
If I can just get the person to have the GUID imbedded in his forehead in Code 39 symbology, I'll get good seats for Armageddon. :-)
Legacy Comments
|
Brett
2004-10-09 |
Really a Key? David, Does the Normal Form rules consider identity or GUID a key? |
|
Richard Tallent
2004-10-09 |
re: DBA's are evil Both an integer or a GUID ID can be candidate keys, but neither is a *natural* key. If anything, the integer is "more natural" because it has the real-world meaning of relative insert order, but that is hardly a meaningful attribute. |
|
DavidM
2004-10-11 |
re: DBA's are evil Brett, Technically Richard is right. The Normal Forms' assume that the key is the "right" key. It's a business decision for what the key and the non-key attributes mean in the enterprise. I have been thinking of creating a "short" table for the person entity.. Say (GUID, FirstName, LastName, Contact). GUID becomes the surrogate & primary key and {FirstName, LastName, Contact} becomes the natural key enforced by unique constraint. Then maybe give the user the option to drop the unique constraint, with a stern warning of course. |
|
Brett
2004-10-12 |
So what does that buy you? You add the contraint, which adds an index. The GUID or IDENTITY would be used for...? RI? The old, "I can change the natural key with no impact to my relational data" argument? Or is it the saving space argument. How about faster access to the data (the one where the indexes are smaller and the joins are plentiful...still don't see how that's faster then going against 1 table). |
|
DavidM
2004-10-12 |
re: DBA's are evil The GUID will be the surrogate key Brett. Yes it will be for RI. You know me Brett, speed, number of joins and size are irrelevant to me during the logical design phase. Besides the ability of the client to create a valid key in a disconnected state, the other reason for not using the other candidate key in RI is far scarier/uglier... Further down the RI chain is the mother of all "junction" tables. The SQL Server limit of 900 bytes for a key becomes a real issue. I am hoping I can implement the composite key constraint but the fact that I have to "create" another key because of an implementation issue is a sad fact of reality. Seriously, any suggestions are welcome. |
|
T
2004-10-19 |
re: DBA's are evil two right |