November 28, 2016 at 5:49 am
Sergiy (11/28/2016)
You must have successfully identified business keys before.So it must be easy for you to show couple of examples (from your experience) where natural keys were just right to identify some entities.
Can you share those examples with us, please?
Not sure what you are looking for since it's difficult to give examples without putting them in a real context. Some very familiar examples of potential business keys are: login names, invoice numbers, order numbers, flight numbers, vehicle registrations. In practice keys have to be derived from business requirements but a technical forum like this one isn't really a good place to review business requirements because everyone here will have different experience in different business domains.
November 28, 2016 at 5:56 am
If you think of it, those SSN, NIN, tax registration numbers, etc., are not natural keys, they all are artificial numbers, auto-generated by some kind of computing system.
They are the same identity numbers, but generated within another system.
By accepting any of those numbers as you "natural" key you:
- assume that your system is always worse in uniquely identifying a person than the system issuing that number;
- inherit all the errors originated from those systems, with no way to fix such an error when it's discovered;
- limit the scope of your system to the scope of the "natural key" issuer.
And you still using an identity number as a key.
Still a surrogate key, only generated elsewhere.
_____________
Code for TallyGenerator
November 28, 2016 at 6:18 am
dalland (11/28/2016)
Some very familiar examples of potential business keys are: login names, invoice numbers, order numbers, flight numbers, vehicle registrations.
1. Not sure what login identifies, apart from login itself. I have 3 different logins in our system, with different sets of privileges. And 1of them may be used not only by me.
2. Invoice, order numbers - aren't they auto-generated sequential numbers? Basically, a form of surrogate key?
Not to mention, they hardly can be labeled unique, as many businesses restart the sequence every financial year.
3. Flight numbers - what do they identify?
And yet again - it's "numbers". Surrogate keys.
Any flight number is made of company code and identity kind of number which has no relevance to any of the "natural" characteristics of the flight (length, direction, duration, etc.)
4. Vehicle registrations are not unique for a car.
You may change your car registration any day, by purchasing a personalised plate.
Not to mention - in many countries registration plates are changed every time a car is sold.
If you want to track the full history of a car it's current registration would not be a reliable key for your search.
_____________
Code for TallyGenerator
November 28, 2016 at 6:28 am
Sergiy,
All data in a database consists of "artificial" symbols assigned by machines or human beings. There are no exceptions. In that very broad sense keys are always surrogates for the things they are assigned to.
In database management however we refer to keys as either surrogate or natural based on something more specific than their perceived "artificiality". The essential feature of a "natural" key is that it is a key that identifies concepts or things in the business domain - meaning the real world outside the database. (Business Key or Domain Key is a better name in my opinion but it means exactly the same thing as Natural Key).
The examples I gave are all potential natural keys. Of course more business context is needed to explain how they might or might not be appropriate in each case but that's a point I made already. Talking about keys divorced of their business context is not very interesting or useful. If/when SSN is used as a key in a database then it would be a natural key too (whether it's a good choice of key or not is a different matter!)
A surrogate key is not used in the business domain. It is internal to the database and therefore is not used to identify business concepts.
November 28, 2016 at 6:57 am
You must have successfully identified business keys before.
So it must be easy for you to show couple of examples (from your experience) where natural keys were just right to identify some entities.
Can you share those examples with us, please?
I already have in an earlier comment. Employee numbers as the most obvious.
November 28, 2016 at 10:05 am
RonKyle (11/28/2016)
You must have successfully identified business keys before.
So it must be easy for you to show couple of examples (from your experience) where natural keys were just right to identify some entities.
Can you share those examples with us, please?
I already have in an earlier comment. Employee numbers as the most obvious.
Any idea what the breakdown was for such employee "numbers" was? Did they base them on some sort of date and sequence number combination? or ???
--Jeff Moden
November 28, 2016 at 10:13 am
Any idea what the breakdown was for such employee "numbers" was? Did they base them on some sort of date and sequence number combination? or ???
There have been multiple systems, mostly incrementing with the ADP software. At a recent site each plant and the corporate office was given an allocation of numbers to manually assign. This did unfortunately result in a duplicate or two, but those were fixed.
November 28, 2016 at 10:32 am
I think context is all with person records. If you are storing medical procedures to be carried out then you absolutely want to make sure the right person has the right limb removed.
If you are selling Thomas the Tank Engine books then accidentally mailing Donald Trump Snr vs Donald Trump Jnr might still result in a sale, just for different reasons.
November 28, 2016 at 10:59 am
David.Poole (11/28/2016)
I think context is all with person records. If you are storing medical procedures to be carried out then you absolutely want to make sure the right person has the right limb removed.
For sure! In the United Kingdom health service the standard identifier for patients is the NHS number.
November 28, 2016 at 12:10 pm
dalland (11/28/2016)
David.Poole (11/28/2016)
I think context is all with person records. If you are storing medical procedures to be carried out then you absolutely want to make sure the right person has the right limb removed.For sure! In the United Kingdom health service the standard identifier for patients is the NHS number.
For all it's faults God bless the NHS
November 28, 2016 at 2:15 pm
One day we'll have a device that can scan a person's DNA and produce a unique and repeatable 16 byte hash code. No name, social security number, date of birth, or other supplemental information will be required from the subject or an external database for an instant positive identification. Change your name, change your sex, lie to us; it simply won't matter.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 28, 2016 at 3:29 pm
dalland (11/28/2016)
Sergiy,All data in a database consists of "artificial" symbols assigned by machines or human beings. There are no exceptions. In that very broad sense keys are always surrogates for the things they are assigned to.
In database management however we refer to keys as either surrogate or natural based on something more specific than their perceived "artificiality". The essential feature of a "natural" key is that it is a key that identifies concepts or things in the business domain - meaning the real world outside the database. (Business Key or Domain Key is a better name in my opinion but it means exactly the same thing as Natural Key).
The examples I gave are all potential natural keys. Of course more business context is needed to explain how they might or might not be appropriate in each case but that's a point I made already. Talking about keys divorced of their business context is not very interesting or useful. If/when SSN is used as a key in a database then it would be a natural key too (whether it's a good choice of key or not is a different matter!)
A surrogate key is not used in the business domain. It is internal to the database and therefore is not used to identify business concepts.
All of the examples you gave are limited to the domains they've been issued in.
They all are surrogate keys issued by the databases which control those domains.
SSN numbers are artificially generated numbers generated by the "Social Security Customers In the USA" database, and it's relevant only within that domain.
Tax numbers are the surrogate keys issued by Registered Tax Payers Database within any particular country, and they have no meaning outside that domain.
National Health System number is an auto-identity generated by the database of Health Care System Customers in the UK, and is useful any withing that domain.
If the system you're designing is meant to be used only within one of those domains, and never expand beyond its scope, then the surrogate key issued by the relevant database must be good for you to use.
But if your system cannot be limited to the scope of any of those big databases (e.g. database of customers of the local hardware shop) then you need to generate your own unique Customer Number.
Which will be as "natural" as any of the numbers generated by those other systems.
_____________
Code for TallyGenerator
November 28, 2016 at 3:40 pm
Sergiy, your approach seems to amount to "everything is a surrogate". There's no point me disagreeing with that. If that's your opinion then I guess this part of the discussion is concluded.
November 28, 2016 at 6:09 pm
dalland (11/28/2016)
Sergiy, your approach seems to amount to "everything is a surrogate". There's no point me disagreeing with that. If that's your opinion then I guess this part of the discussion is concluded.
Surrogate key is some number or any other kind of value auto-generated by some system (mostly computer systems these days) which has no correspondence to any property of the object it's meant it identify.
Would you agree with such definition of the surrogate key?
Does SSN fit this definition?
_____________
Code for TallyGenerator
November 28, 2016 at 6:23 pm
Uh, no. It sure seems like it could be, but... in practice, it's probably a bad idea, as there's a lot of real-world semantics and other implications that come along with US SSNs (think: stolen bank and insurance statements in mailings that used SSN as part of account numbers...in the clear, and that even masking to last 4 digits is still enough to figure out full SSNs).
Credit Card #s would be similarly baggage-equipped "natural keys", too, right?
Viewing 15 posts - 61 through 75 (of 89 total)
You must be logged in to reply to this topic. Login to reply