August 14, 2012 at 11:05 am
L' Eomot Inversé (8/14/2012)
Jeff Moden (8/13/2012)
Lynn Pettis (8/13/2012)
Jeff Moden (8/13/2012)
I consider CustomerID's and EmployeeID's that are generated simply by the likes of an IDENTITY column, to be a surrogate key even when exposed to the end user.But EmployeeIDs generated by the likes of an identity column are very rare, apart from the examples in texbooks written by the clueless, and in databases owned by businesses who have the misfortune to possess staff who trust in such textbooks. Simple sequential numbering was already rare right back in the 60s, in the days of manual payroll and manual personnel records, because the problems simple sequential numbering caused were already recognised.
Typically a EmployeeID could be a string of characters (often decimal digits, but sometimes alphanumeric) that incorporated several meaningful substrings: a substring identifying the location/office which originally recruited the employee; a substring allocated by the recruiting office as a serial number (so 0005 would mean the 5th person recruited by this office); a substring acting as validity check for the rest (perhaps modulo 97 checkdigits, or something longer); and perhaps other substrings - after the first merger or take-over, the system would incorporate a substring identifying the company which originally recruited the employee. This made it fairly easy to cater for reassigning employee neumbers on merger or takeover - the company code part of the EmployeeID would indicate what the format of the rest of the structured ID was, so the only change on take-over was to stick a company identifier in front of existing IDs for employees of the company being taken over; on merger, the company id might be added to employeeIDs from both companies; and of course compay IDs could be structured themselves, so chains of take-overs and mergers were OK too.
I first came across structured EmployeeIDs in a brief summer vacation job before I went to university - can't remember exactly which year, but it was either 1961 or 1962, and the structured employeID seemed to be old hat already then - long before we had any hierarchic or network or relational databases; probably it originated long before there was any concept of a computer-based database (the term "database" itself dates from the early 60s, I think).
If people are using employeeIDs generated as values in an IDENTITY column with no internal coding and making them visible to users they will need to stop using that the first time anything interesting happens; it really doesn't work, long term. And I would agree with you that these are visible surrogate keys. It doesn't strike me as at all a good idea to use them.
Here's the thing I've noticed in several enterprise level DB systems. Take a Patient ID in a medical software, or an Employee ID in some other software. There is nothing, zip zero nada, that you can gather from a patient that would uniquely identify them. I can relate many of examples and usages where even taking a natural key of Name, SSN (in the US), Address, and DOB you get duplicates. Using an arbitrary numbering scheme, independently assigned, and guaranteed to be unique is beneficial. What you won't be able to do is have the ability to modify that number. Many places use both the Surrogate key (identity based) and a natural key (or a Visible Surrogate Key). The Identity based key is to ensure that you keep consistency and don't orphan records. The natural/Visible key is to show to the user and to allow it to be changed as the business sees fit to accommodate it's business environment.
The example that was used before of Merging multiple companies, the fact is that one of the systems will be REQUIRED to change it's numbering/recording scheme to fit the other company. If you are using an Identity based key, all you need to do is create a crosswalk from the new Identity to the old system's key and you automagically can find all relations as the data is being ported over without it being a breaking change. Yes, I've dealt with UPIN/PIN to NPI conversions and we're now dealing with ICD9 to ICD10 conversion, these are things where having the Identity based surrogate key and/or the visible natural/surrogate key helps a lot... it removes the worry about a user f---'ing up the entire system by a wrong keystroke.
August 14, 2012 at 11:09 am
Lynn Pettis (8/14/2012)
Brandie Tarvin (8/14/2012)
I get ideas from the weirdest places. I keep threatening to write stories about you people and never do. Well, this debate has inspired me to come up with a first draft quickie and I have decided to present it for your reading entertainment. @=)"The Great Key Debate" by Brandie Tarvin (copyright me, of course)
Thunder rumbled overhead, a clear warning to innocent bystanders of the storm's inherent danger. But they chose to ignore it, captivated by the ongoing debate in Thread Town Square.
Lynn and Tom stood on opposite ends of the drought-stricken meadow, a parched forum of brittle humors and harsh comments. Yellowed backgrounds of aged LCD crackled underfoot as each adjusted their positions, hands at the ready, hovering waist-high above their weapons of choice. Hard eyes glinted, staring with laser precision into their opponent. If looks could kill, they'd have bored holes into each other's brains long ago.
An electron whispered. A packet dropped. The bystanders crowded close as debate ended and conflict began. The digital wind howled in dirge-like tones. Fingers twitched, breathing hitched.
Without warning, keyboards clattered, pages and pages of text filling the air. The tornado chaos flung spaghetti code rants across internet skies of screen-death blue. The posts slammed down upon Thread Town Square so fast and furious the spectators could barely keep track.
Then it happened. Out of thin air, someone pulled out the Ultimate Weapon. Books Online crashed through the barriers of real space, shattering the concentration of hundreds. Lynn and Tom looked up.
It was the last sight they ever saw.
Okay, Brandie, here is an idea that you probably already had. We all know the stories of the Knights Templar, the Men in Black, add your own secret society. We also know that on this forum people have asked how to keep the DBA from seeing the data stored in the database. We need a story where the secret society is in fact the DBA's entrusted with the keys to societies data. Would be an intriguing story line, just too bad I don't have the wordsmith skills to pull it off.
And, of course, one of the secret society's most powerful weapons is DBCC Timewarp
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 14, 2012 at 12:16 pm
Stefan Krzywicki (8/14/2012)
Lynn Pettis (8/14/2012)
Brandie Tarvin (8/14/2012)
I get ideas from the weirdest places. I keep threatening to write stories about you people and never do. Well, this debate has inspired me to come up with a first draft quickie and I have decided to present it for your reading entertainment. @=)"The Great Key Debate" by Brandie Tarvin (copyright me, of course)
Thunder rumbled overhead, a clear warning to innocent bystanders of the storm's inherent danger. But they chose to ignore it, captivated by the ongoing debate in Thread Town Square.
Lynn and Tom stood on opposite ends of the drought-stricken meadow, a parched forum of brittle humors and harsh comments. Yellowed backgrounds of aged LCD crackled underfoot as each adjusted their positions, hands at the ready, hovering waist-high above their weapons of choice. Hard eyes glinted, staring with laser precision into their opponent. If looks could kill, they'd have bored holes into each other's brains long ago.
An electron whispered. A packet dropped. The bystanders crowded close as debate ended and conflict began. The digital wind howled in dirge-like tones. Fingers twitched, breathing hitched.
Without warning, keyboards clattered, pages and pages of text filling the air. The tornado chaos flung spaghetti code rants across internet skies of screen-death blue. The posts slammed down upon Thread Town Square so fast and furious the spectators could barely keep track.
Then it happened. Out of thin air, someone pulled out the Ultimate Weapon. Books Online crashed through the barriers of real space, shattering the concentration of hundreds. Lynn and Tom looked up.
It was the last sight they ever saw.
Okay, Brandie, here is an idea that you probably already had. We all know the stories of the Knights Templar, the Men in Black, add your own secret society. We also know that on this forum people have asked how to keep the DBA from seeing the data stored in the database. We need a story where the secret society is in fact the DBA's entrusted with the keys to societies data. Would be an intriguing story line, just too bad I don't have the wordsmith skills to pull it off.
And, of course, one of the secret society's most powerful weapons is DBCC Timewarp
+1! Gotta fix those issues where someone slipped up, ala Quantum Leap
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 14, 2012 at 12:19 pm
I really, really, REALLY want to ask some of these people if they even know how to think.
August 14, 2012 at 12:24 pm
Lynn Pettis (8/14/2012)
I really, really, REALLY want to ask some of these people if they even know how to think.
They would say "yes", but only because they don't know how to think about the question.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 14, 2012 at 1:48 pm
venoym (8/14/2012)
...and we're now dealing with ICD9 to ICD10 conversion, ...
You are only now converting to ICD10? Those have been mandatory in South Africa (where I live) for the last five years or so. And I thought we're behind the rest of the world :hehe:
August 14, 2012 at 2:02 pm
Jan Van der Eecken (8/14/2012)
venoym (8/14/2012)
...and we're now dealing with ICD9 to ICD10 conversion, ...
You are only now converting to ICD10? Those have been mandatory in South Africa (where I live) for the last five years or so. And I thought we're behind the rest of the world :hehe:
I personally converted to the ID-Ten-T encoding years ago, all my programming is a lot simpler but it does take improved stupidity to break my code these days...
... oh, wait, wrong standards?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 14, 2012 at 2:48 pm
Evil Kraig F (8/14/2012)
Jan Van der Eecken (8/14/2012)
venoym (8/14/2012)
...and we're now dealing with ICD9 to ICD10 conversion, ...
You are only now converting to ICD10? Those have been mandatory in South Africa (where I live) for the last five years or so. And I thought we're behind the rest of the world :hehe:
I personally converted to the ID-Ten-T encoding years ago, all my programming is a lot simpler but it does take improved stupidity to break my code these days...
... oh, wait, wrong standards?
+10T
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 14, 2012 at 3:56 pm
Off to Sri Lanka tomorrow, to attend eldest son's wedding. The evil child has insisted I be kilted for the event, and I can no longer get into my lightweight kilt (that kit hasn't matched my middle-age spread) so I will be in a heavyweight kilt in the tropics - but apart from that the trip should be fun, at least weather will probably be better than UK.
Not taking laptop with me, on pain of severe ill-treatment by wife, so I will be completely starved of SQLServerCentral for the duration (so that makes two members of the family inflicting pain on me on this trip; all I need now is for the other two sons to join in). Should be online again and recovering from this deprivation on 30 Sep.
Tom
August 14, 2012 at 4:04 pm
Lynn Pettis (8/14/2012)
I really, really, REALLY want to ask some of these people if they even know how to think.
Right now Lynn I'm just very glad I didn't say a blessed word when you were dealing with a frustrating person. Go go gadget textwall.
Enjoy Sri Lanka, Tom.
#topicchange
Anyone started following the pre-season games for American Football yet?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 14, 2012 at 4:04 pm
August 14, 2012 at 4:09 pm
L' Eomot Inversé (8/14/2012)
Off to Sri Lanka tomorrow, to attend eldest son's wedding. The evil child has insisted I be kilted for the event, and I can no longer get into my lightweight kilt (that kit hasn't matched my middle-age spread) so I will be in a heavyweight kilt in the tropics - but apart from that the trip should be fun, at least weather will probably be better than UK.Not taking laptop with me, on pain of severe ill-treatment by wife, so I will be completely starved of SQLServerCentral for the duration (so that makes two members of the family inflicting pain on me on this trip; all I need now is for the other two sons to join in). Should be online again and recovering from this deprivation on 30 Sep.
Have a good trip! Congratulations to you and your son.
August 15, 2012 at 6:30 am
Lynn Pettis (8/14/2012)
L' Eomot Inversé (8/14/2012)
Off to Sri Lanka tomorrow, to attend eldest son's wedding. The evil child has insisted I be kilted for the event, and I can no longer get into my lightweight kilt (that kit hasn't matched my middle-age spread) so I will be in a heavyweight kilt in the tropics - but apart from that the trip should be fun, at least weather will probably be better than UK.Not taking laptop with me, on pain of severe ill-treatment by wife, so I will be completely starved of SQLServerCentral for the duration (so that makes two members of the family inflicting pain on me on this trip; all I need now is for the other two sons to join in). Should be online again and recovering from this deprivation on 30 Sep.
Have a good trip! Congratulations to you and your son.
Have a great time Tom. You can always get your own back by telling the usual embarrising "when he was a baby" stories...
Rodders...
August 15, 2012 at 8:28 am
Stefan Krzywicki (8/14/2012)
Lynn Pettis (8/14/2012)
I really, really, REALLY want to ask some of these people if they even know how to think.They would say "yes", but only because they don't know how to think about the question.
http://www.technologyreview.com/view/409043/how-to-think/[/url]
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
August 15, 2012 at 10:13 am
Evil Kraig F (8/14/2012)
Lynn Pettis (8/14/2012)
I really, really, REALLY want to ask some of these people if they even know how to think.Right now Lynn I'm just very glad I didn't say a blessed word when you were dealing with a frustrating person. Go go gadget textwall.
Enjoy Sri Lanka, Tom.
#topicchange
Anyone started following the pre-season games for American Football yet?
Yes - not intently, but to at least start watching
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 37,486 through 37,500 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply