Meaningful Keys
-
@Weng said:
@havokk said:
Besides, sometimes there is no natural key. For example, what natural key would you choose to uniquely identify a person?
DNA sequence + birth order.That covers identical twins, but what about those who are genetic mosaic or chimeras (more than one DNA sequence, either in patches or evenly mixed throughout)? Which do you record?
-
@locallunatic said:
That covers identical twins, but what about those who are genetic mosaic or chimeras (more than one DNA sequence, either in patches or evenly mixed throughout)? Which do you record?
The reproductively active sequence. Of course, there might be some chimeric cases where testicles and/or ovaries are split, but obviously we should require removal of one reproductive organ in these cases.
-
I used to have access to the database of Blue Shield of California... Social Security Numbers are not unique, especially in California. In fact, there is no unique data point about people when you get enough of them in the same database. I've never seen a people database that didn't require a surrogate key, usually the internal customer number or policy number or whatever. So... I'm a little amused by this thread. One question - how much practical database programming experience do people have? I have 20 years - this isn't a challenge, just something I'm interested in.
-
@jasmine2501 said:
…there is no unique data point about people when you get enough of them in the same database.
That is why it is my counter-example to database designers who are against surrogate keys.
I follow Pascal's advice of using surrogates only when there are no natural identifiers, or there are composite keys which complicate access and referential integrity.
-
Both problems are equally evil. Using a surrogate key when there is a good natural key is just as bad as not using one when there isn't a good natural key. In one of my databases, there is an identity column on every table. When I asked why, they said "company policy" - this is the same company who said "because it causes errors in the client apps" when I asked why they didn't have any constraints or foreign key enforcement. And we pay them about 3 million dollars a year.
-
@jasmine2501 said:
Both problems are equally evil. Using a surrogate key when there is a good natural key is just as bad as not using one when there isn't a good natural key. In one of my databases, there is an identity column on every table. When I asked why, they said "company policy
Again I am the devils advocate.
in our (in house developed) system we also have identity columns on every table named "[table_name]_id".
the reason was not "company policy", but our framework (list and form generators). by having surrogate keys, we save ourselves a lot of development time. you could say it's convention over configuration.
the system can generate a lot of boilerplate code automatically with little input.
i also worked on a project where we had a "persons" table. The persons were primarily managed by SAP and had a unique SAP ID. Additionally some other vital data for a single person was in another system with another unique id. And persons had personel cards as well, with, guess what, yet another personel unique id.
so we created a table for them with yet another unique identifier :)
it really helped, because as it turned out there were persons, who were not in SAP, but had a personel card and vice versa.
TLDR; when working for large companies, never rely on their internal systems, they are always a mess
