Re: Modeling/Constraint question
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sun, 1 Jun 2008 19:11:08 +0000 (UTC)
--CELKO-- (jcelko212@xxxxxxxxxxxxx) writes:
Yeah, but can a relational database live with 3% duplicates in itsprimary key? <<
By definition, there are no duplicates in the keys; they are in the
data.
So why then asked about the natural key for a person, do you start
yiddling about 3% acceptable error rate?
We are in a relational database, and for technical reason we don't
accept any duplicates. But as you notice, in the real world they are
unavoidable in practice. Enter: the surrogate key.
No, the email is the way to get to a person (in the ISO sense of a
lawful person, human or otherwise) who plays the role of "customer"
for them. We are back to the idea of a role.
The question was about persons, not roles. When you asked about the key
for a person, you suggested "e-mail address". You now admit that it is
not the key for a person, but for something else.
No, it isn't. Besides the design fallacy of confusing the
representation with the fact being modeled, it is a hell of lot easier
to screw up an integer than an email address that has validation and
verification rules. How did you confirm that Cindy Lou Who was really
number 42? (Pardon the Dr. Seuss reference)
On most invoices I get, there is a customer number. Apparently many
organisations think that assigning numbers to their customers is a good
idea.
Sure, someone may confuse a number, but why would e-mail addresswa be
less prone to confusion? They are typically longer, often include
people's name that may be difficult to spell, so there are more
chances for things to go wrong. Systems with case-sensitive collations
have their own problems.
And even if an application may use e-mail address or whatever as the
exposed key, you can be fairly sure that they also have a surrogate
that they do not expose. That makes things a little easier when a
customer changes his e-mail address.
Any one who has worked with data modeling in real-life system will this
to persons who only have done data modeling in theory. <<
Sorry, but I have a fair number of implemented data models under my
belt, AND I teach the theory.
This isn't about classroom theory, Joe. This is about real world.
Expecting a Universal, Magical identifier for "objects",
"relationships", "persons", etc. is very much a "newbie question"
And it's very much a newbie answer to suggest that a surrogate such
an IDENTITY is not an acceptable key for a person.
while exposed physical locators like GUIDs, IDENTITY
None of them are physical locators, and you know that very well. Since
you blantantly repeat this lie which is very easily demasked by the
SQL Server community, why should believe anything else you say?
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Follow-Ups:
- Re: Modeling/Constraint question
- From: --CELKO--
- Re: Modeling/Constraint question
- References:
- Re: Modeling/Constraint question
- From: --CELKO--
- Re: Modeling/Constraint question
- Prev by Date: Re: Problem with joins in SQL.
- Next by Date: where do I download sql odbc drivers from?
- Previous by thread: Re: Modeling/Constraint question
- Next by thread: Re: Modeling/Constraint question
- Index(es):
Relevant Pages
|
