November 28, 2016 at 8:42 pm
Sergiy (11/28/2016)
Would you agree with such definition of the surrogate key?
No I would not a agree, as I already explained.
November 28, 2016 at 10:15 pm
dalland (11/28/2016)
Sergiy (11/28/2016)
Would you agree with such definition of the surrogate key?No I would not a agree, as I already explained.
You mean this?
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.
Sorry, it does not make much sense.
Can you explain what is the meaning of SSN outside of the US Social Security database?
If I bring you a number which looks like an SSN, and tell you it's my SSN - what would be a significance of that number, if you have no access to the database or any of its verified replicas?
Step into the real world - go to Europe. Better to the Eastern part of it.
And try to use any reference to your SSN there.
They'll quickly make you understand that that number has no meaning in the real world, outside of the scope of your social security coverage.
SSN is as good and as useful as an identity value generated within your database.
Both those identity numbers have any meaning only where the central database or its replicas are accessible and trusted.
Same thing.
_____________
Code for TallyGenerator
November 29, 2016 at 12:59 am
David.Poole (11/28/2016)
...For all it's faults God bless the NHS
Absolutely, it could be better1 but we would be worse off healthwise and culturally without it.
1What is "better" is better left discussed on other forums as not much of it relates to SQL Server 😉
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 29, 2016 at 8:47 am
Sergiy (11/28/2016)
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.
In the case of a car, the VIN is though, and would be the most sensible key to use if you're going to be creating a database holding car data, if you have that data, of course. Sometimes there are accepted industry (or other) defined natural keys that it does make sense to use - rather then, say, add a GUID column to the table. On the other hand, when I see situations where someone is crowing about their Successful Quest For A Natural Key which finally turned up a 7 column composite key which they lovingly implenented in their physical model (on dbDebunk IIRC), my crystal ball (no - not that one) kinda has me thinking of
http://www.gocomics.com/calvinandhobbes/1990/07/11
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
November 29, 2016 at 12:26 pm
Sergiy (11/28/2016)
Sorry, it does not make much sense.
Can you explain what is the meaning of SSN outside of the US Social Security database?
If I bring you a number which looks like an SSN, and tell you it's my SSN - what would be a significance of that number, if you have no access to the database or any of its verified replicas?
Step into the real world - go to Europe. Better to the Eastern part of it.
And try to use any reference to your SSN there.
They'll quickly make you understand that that number has no meaning in the real world, outside of the scope of your social security coverage.
SSN is as good and as useful as an identity value generated within your database.
Both those identity numbers have any meaning only where the central database or its replicas are accessible and trusted.
Same thing.
This depends on source and target and how the information is being used. I would consider a surrogate key anything that is generated in the target system as that will be dependent on whatever rules are in place in the target system, however in the case of say SSN if i decide that for the purposes of whatever application is using the database it's a key it is not a surrogate key. In that case it would be a key that is naturally occurring the source data whether that's someone hand keying it in or it's coming from multiple data imports or what have you.
November 29, 2016 at 1:32 pm
Charles Kincaid (11/20/2016)
I do have a slight case for denormalization. I want to have an unchangeable representation of something historical. Where did we ship that order and to whom did we bill that. Because a customer moves I don't want that to affect where we did ship their stuff in the past. That is why there is OLTP and data warehousing.
It's easy to do that without denormalising. It can be done for example with a customer previous delivery address history table (or you might choose to hold the current address that history table in which you would want to drop "previous" from the table's name) or you could adopt the version of 6NF that was designed to cope with attributes whose history had to be remembered (not the DKNF version of 6NF), and there are other techniques that do the job using complete normalisation.
And adresses are not the only things that change, peoples' names change too (for example change of name on marriage and/or on divorce, name change when adopting a new religion, name change on being awarded a title of nobility, name change by deed poll or other legal games) and some of your customers may use several names at the same time (professional name and ordinary name and some people live in multilingual socieies where different names are used in different laguages) - there are many things that will come back to bite you if you don't ensure that things that can change re not treated as thiongs that can't change.
One case against IDENTITY columns was that there won't be enough numbers to last the lifetime of the system. Wrong by half. Don't start them at zero. "Don't those huge numbers take up more room than the small ones?" No. "Won't the user be confused by the minus sign?" No. The USER will never see the *&%-ing minus sign!. It's an ID column that is never exposed. (Thank you, Joe.)
Will a factor of 2 be enough, or are you going to end up having to change the surrogate key column (which you use for foreign key constraints) from int to bigint in all the tables in which it occurrs?
Tom
November 29, 2016 at 2:24 pm
ZZartin (11/29/2016)
This depends on source and target and how the information is being used. I would consider a surrogate key anything that is generated in the target system as that will be dependent on whatever rules are in place in the target system, however in the case of say SSN if i decide that for the purposes of whatever application is using the database it's a key it is not a surrogate key. In that case it would be a key that is naturally occurring the source data whether that's someone hand keying it in or it's coming from multiple data imports or what have you.
Exactly my point.
If you design an application specifically for SS registered US citizens, then SSN as a key would be probably a good choice.
If you design a, say, a system for Les Mills then you better take Les Mills Membership Number as a key, rather than SSN, as some members of Les Mills might be not US citizens.
But if you design a database for a local hardware shop, which does not have published customer registration number then you need to create one by yourself. The most convenient option would be an identity field on Customer table.
All 3 examples above are equally surrogate or natural, name them as you wish.
All 3 are generated by an auto-algorithm at the registration point, and all are valid only within the scope of their domains.
_____________
Code for TallyGenerator
November 29, 2016 at 2:29 pm
Phil Factor (11/22/2016)
There's nothing that says a table can't have more than one unique index on it.
it certainly can. It is no crime but it is a sign that there may be a problem with normalisation to have more than one candidate key, surely
Three comments on that:
1) Multiple unique indexes don't always imply multiple candidate keys - for that you need multiple unique indexes where all the indexed columns are declared NOT NULL.
2) When a natural primary key is long and complex there will be a surrogate key, the table defining that surrogate key will have at least two candidate keys (the natural key and the surrogate), and that is certainly not bad design since it will save space and time dealing with foreign key constraints (and joins),
3) Multiple candidate keys don't generally imply that the tables and their constraints are unable to forbid data modifications that violate data integrity, so they are not generally indicative of a problem with normalisation unless you think that normalisation is something other tan arranging that constraints prevent violaton of data integrity.
Tom
November 29, 2016 at 2:35 pm
SSN as a key would be probably a good choice
Using an SSN as a key is never a good idea, except perhaps for the Social Security administration. The number requires too much legally mandated protection to be useful as a key.
November 29, 2016 at 2:38 pm
David.Poole (11/28/2016)
http://www.publications.parliament.uk/pa/cm200405/cmselect/cmconst/243/24307.htm paragraph 92 for us in the UK
well, para 92 makes it pretty clear than our NI "numbers" are a total mess. I wonder if our other national identity (the NHS number) is any better.
But I'm shocked by the idea that SSN could be treated as a valid key (whether considered surrogate or natural) because some SSNs were issued twice a few decades ago, and some of those may well still be in use.
Tom
November 29, 2016 at 4:14 pm
In the case of a car, the VIN is though,
It should be and would be but for vehicle cloning
November 29, 2016 at 9:10 pm
TomThomson (11/29/2016)
David.Poole (11/28/2016)
http://www.publications.parliament.uk/pa/cm200405/cmselect/cmconst/243/24307.htm paragraph 92 for us in the UKwell, para 92 makes it pretty clear than our NI "numbers" are a total mess. I wonder if our other national identity (the NHS number) is any better.
But I'm shocked by the idea that SSN could be treated as a valid key (whether considered surrogate or natural) because some SSNs were issued twice a few decades ago, and some of those may well still be in use.
I believe it was that some individuals received two SSNs rather than the SSNs being issue twice. I certainly could be wrong but here's the article covering what I'm talking about. Any citation for the double issuing of the same numbers?
http://www.cnsnews.com/news/article/
--Jeff Moden
November 30, 2016 at 9:40 am
First, not everyone has a SSN, and another problem is that it's not verifiable. How do I know that John Smith's SSN is 111-22-3333?
If you identify someone by their email or phone number, then there are ways to match that up with a name, and you can also use that same contact ID for use as confirmation (ie: click on the link or press the # key to confirm your registration).
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 30, 2016 at 1:24 pm
Jeff Moden (11/29/2016)
I believe it was that some individuals received two SSNs rather than the SSNs being issue twice. I certainly could be wrong but here's the article covering what I'm talking about. Any citation for the double issuing of the same numbers?
Your link is a little broken, but I guess you meant http://www.cnsnews.com/news/article/government-gave-4317-aliens-2-social-security-numbers-piece
That's not wht I was thinking of. Somewhere (I think on a government site) I read that in the early years, when issuing was all manual (no computers) various issuing authorities (at county level? it was something a lot smaller than state, anyway) were given ranges of numbers they could use; but when the rage ran out, some authoritites just carried on with the next number in sequence instead of asking for a new range, ane thus issued numbers in someone else's range, and sometimes that someone else had also issued those numbers.
But I can't find the reference now.
Of course there is hordes of news about SSNs that are used by more than one person, many because one of those persons had invented himself an SSN and some because it was part of an attenmpt at identity theft. The identity theft cases are presumably result of misuse of SSN as identifier (which I believe was illegal at some time, and may still be, although it was never enforced by more than some words on an SSN document saying it must not be used except in connection with Scoial Security and is not even enforced by that now).
Tom
November 30, 2016 at 1:39 pm
Jeff Moden (11/28/2016)
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 ???
One place I worked used an employee identity number that was based on two things: identifier (two decimal digits) of the component of the company which originally recruited the employee and serial number (4 decimal digits) of recruitment within that component. When I joined that company, the components were called organisations, later they were called divisions, which created no end of confusion (as previously organisations had benn split into divisions) but the personnel guys kept the numbering working just fine, and organised everyone's job title change to fit the new component names, and carried on helping with recruitment and training and so on without any noticeable disruption (that was in the days when personnel departments did useful things, before they became those useless entitities "human resource departments" who were far too important to do anything useful.
Tom
Viewing 15 posts - 76 through 89 (of 89 total)
You must be logged in to reply to this topic. Login to reply