November 25, 2016 at 9:57 am
That was exactly what I was asking about - can you show those cases when a natural key is the best choice?
I've worked in companies with a reliable employee number that was not the SSN. In those cases, I have used the employee number.
November 25, 2016 at 10:14 am
I work for health insurance company. one of the challenges, at least with our aystem, is reliably coordinating people with membership. people come and go into the plans, have dual coverage, non-Medicare to Medicare... even with the data we have with ppl it is difficult. We do try though. With healthcare, it isnt very responsible to just "oh well" it like it's another Amazon account or SSC membership...
November 26, 2016 at 1:08 pm
RonKyle (11/25/2016)
That was exactly what I was asking about - can you show those cases when a natural key is the best choice?
I've worked in companies with a reliable employee number that was not the SSN. In those cases, I have used the employee number.
I've been in companies with the exact same setup. In the case where the employee number is stored as a number, it worked great. When the employee number was stored as an nvarchar, it didn't work as well.
November 26, 2016 at 4:45 pm
Storing guids in things like RedShift or Vertica is fun. Not only do you have to store them as CHAR(36) but by definition they are unique and don't compress.
Certain person identifiers you'd think/hope would be unique. Unfortunately the scheme for generating them pre-dates the purpose we are now wanting to put them to, so those identifiers tend to get recycled.
November 26, 2016 at 7:09 pm
David.Poole (11/26/2016)
Storing guids in things like RedShift or Vertica is fun. Not only do you have to store them as CHAR(36) but by definition they are unique and don't compress.Certain person identifiers you'd think/hope would be unique. Unfortunately the scheme for generating them pre-dates the purpose we are now wanting to put them to, so those identifiers tend to get recycled.
That reminds me... Just in case you run into someone that says so, the Social Security Administration say it does NOT recycle SSNs. Please see Q20 at the following official SSA URL.
https://www.ssa.gov/history/hfaq.html
--Jeff Moden
November 26, 2016 at 7:30 pm
Sergiy (11/25/2016)
RonKyle (11/23/2016)
What is you natural key for "person" records?
In most cases there isn't one. I would certainly never use an SSN for this. But that doesn't mean that this is true for all things. I've seen integers (and guids) used when there was a clear and obvious choice for a key.
That was exactly what I was asking about - can you show those cases when a natural key is the best choice?
P.S. When somebody mentioned SSN I always want to remind:
There are several billions people who never were and never will be associated with SSN.
Worth keeping it in mind.
Real sore spots with me on both.
The anti-Identity column folks insist that you can create a reliable natural key based on parts of other columns or even a composite key made from those columns. Apparently, they've never visited a place predominately inhabited by ethnic groups with last names like Smith or Costa. We had something like 30 folks working at a company I worked at in Rhode Island all with the last name of Costa. 6 of them had the first name of Manual and 3 of them had the same middle name, as well. Then there were the ladies... I can't remember the counts but several were named Mary and several were named Pamela. Again, a couple of them even had the same middle name.
As for SSNs, even if they were large enough to handle the whole world, I wouldn't use them but once (properly salted and encrypted) anywhere in the database. It's not likely that we'll need more than 9.2 Quintillion identity values even in the next 5000 years. Heh... of course, we don't have to worry about that. Stephen Hawking says the race of homo sapiens is going to snuff itself out sometime in the next thousand years. 😉
Stephen Hawking Warns We Only Have 1000 Years Left On Earth
--Jeff Moden
November 27, 2016 at 9:13 am
RonKyle (11/23/2016)
What is you natural key for "person" records?
In most cases there isn't one.
I really have trouble understanding why you would say that. A lot depends on exactly what the context of a "person" is of course. In fact the question is pretty much unanswerable without such context. "People" might really be employees or customers or users and in those contexts you will find employee numbers, account numbers, login names or similar things used as natural keys.
Of course it's true that conceivably a single person could join a company or organisation twice and end up with two different employee numbers but if that mattered to the employer (maybe it does, maybe it doesn't) then presumably the employer would have to have mandatory business processes that sought to prevent that happening. In a similar way, banks have "know your customer" processes to try to ensure proper identification of individuals. The business requirement is what defines the key(s) and business interactions involving people are no different in that respect to anything else. The idea that those interactions with people don't have or won't need a natural key doesn't match with my experience.
I believe that in many (most?) cases employers are legally required to record tax identifiers for their employees and surely most employers would not want to allow duplicate tax identifiers into the system. So certainly those tax identifiers may become a key depending on the context and requirements.
November 27, 2016 at 9:20 am
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
Multiple candidate keys in a table could be just a sign of thorough analysis and good design. Some things do have more than one key. Having multiple keys certainly doesn't indicate a "problem" with normalization because normalization treats all keys in a table as equally important. Even Sixth Normal Form allows for a table to have more than one key.
November 27, 2016 at 2:01 pm
dalland (11/27/2016)
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
Multiple candidate keys in a table could be just a sign of thorough analysis and good design. Some things do have more than one key. Having multiple keys certainly doesn't indicate a "problem" with normalization because normalization treats all keys in a table as equally important. Even Sixth Normal Form allows for a table to have more than one key.
Agreed. Especially when you want to move away from composite keys. For example... we have a "Loans" table (pluralization of the name certainly NOT my idea). It has a key which must be unique consisting of a "Loan_Number" and "Client_ID" pair of columns. We have it uniquely constrained as an "alternate key".
We have a "Loan_ID" column on the same table which acts both as the PK and the Clustered Index.
The alternate key was never meant to be used anywhere else because it doesn't always follow the rules of a PK. Loan_Numbers and/or Client_ID can and have changed during mergers or loan sales. The really bad part is that my predecessors thought it easier to use the Loan_Number and Client_ID in other tables instead of the rock steady Loan_ID. Worse yet, they had no enforced standards. You'd be surprised at how many different ways people will name columns for Loan_Number and Client_ID.
As a result, any changes to the alternate key require days of information gathering, testing, etc, etc, instead of going to just one table and changing it.
Unfortunately, that seems the "normal" thing to do for a lot of people and it makes life bloody difficult.
The term "Normal" is normally equated to the terms "FUBAR" and "SNAFU" from what I have seen in the past. The future isn't looking so good either. :sick:
--Jeff Moden
November 27, 2016 at 6:51 pm
In most cases there isn't one.
I really have trouble understanding why you would say that.
Ok, I'll change it to "in many cases." My point was that it seems to be that some people use the harder cases to justify universal use of identities rather than even try to identify a business key.
November 28, 2016 at 12:27 am
Jeff Moden (11/26/2016)
David.Poole (11/26/2016)
Storing guids in things like RedShift or Vertica is fun. Not only do you have to store them as CHAR(36) but by definition they are unique and don't compress.Certain person identifiers you'd think/hope would be unique. Unfortunately the scheme for generating them pre-dates the purpose we are now wanting to put them to, so those identifiers tend to get recycled.
That reminds me... Just in case you run into someone that says so, the Social Security Administration say it does NOT recycle SSNs. Please see Q20 at the following official SSA URL.
http://www.publications.parliament.uk/pa/cm200405/cmselect/cmconst/243/24307.htm paragraph 92 for us in the UK
November 28, 2016 at 3:02 am
RonKyle (11/27/2016)
In most cases there isn't one.
I really have trouble understanding why you would say that.
Ok, I'll change it to "in many cases." My point was that it seems to be that some people use the harder cases to justify universal use of identities rather than even try to identify a business key.
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?
_____________
Code for TallyGenerator
November 28, 2016 at 3:56 am
David.Poole (11/28/2016)
http://www.publications.parliament.uk/pa/cm200405/cmselect/cmconst/243/24307.htm paragraph 92 for us in the UK
Thanks for the official confirmation that in the UK not everyone has a National Insurace Number (NIN) nor is every unique NIN issued to a single individual.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 28, 2016 at 4:10 am
Hmm...
I still wouldn't count on US SSNs being unique over time, especially when it comes to immigration-related ones. But that's me and my limited experiences...
then, throw in IRS and tax IDs for corporations (9 #s, but different pattern of "-" separators), and...
it gets crazy when they get mixed up together.
So, for me? I'm happy with good ol' "SQL Pointers" (identity fields), with all due respects to Joe Celko...
November 28, 2016 at 5:21 am
David.Poole (11/28/2016)
Jeff Moden (11/26/2016)
David.Poole (11/26/2016)
Storing guids in things like RedShift or Vertica is fun. Not only do you have to store them as CHAR(36) but by definition they are unique and don't compress.Certain person identifiers you'd think/hope would be unique. Unfortunately the scheme for generating them pre-dates the purpose we are now wanting to put them to, so those identifiers tend to get recycled.
That reminds me... Just in case you run into someone that says so, the Social Security Administration say it does NOT recycle SSNs. Please see Q20 at the following official SSA URL.
http://www.publications.parliament.uk/pa/cm200405/cmselect/cmconst/243/24307.htm paragraph 92 for us in the UK
Yowch. That's gotta be a computational hoot.
--Jeff Moden
Viewing 15 posts - 46 through 60 (of 89 total)
You must be logged in to reply to this topic. Login to reply