Re: Separate foreign keys with shared ID space
Date: Sun, 1 Aug 2004 12:17:39 +0000 (UTC)
Message-ID: <Xns953891274E366Yazorman_at_127.0.0.1>
Howard J. Rogers (hjr_at_dizwell.com) writes:
Two cases:
> "Erland Sommarskog" <esquel_at_sommarskog.se> wrote in message
> news:Xns9538D45EA661Yazorman_at_127.0.0.1...
>> So you use SSN. Now you want to enter me in your database. What is my
>> SSN?
>
> I'm not entirely sure what point you're making. Either you have one, in
> which case I must know it, and then I can enter your details. Or you don't
> have one, in which case, I can't enter your details.
Well, if the system was designed by someone who religiously believed in natural keys you have a problem.
Of course, there are systems in which it's perfectly reasonable to use
person identifaction numbers as the key. The Swedish population registry
for example. But in this case it makes sense, because this is the database
that assigns the number - so in essence, here you have a sequence number.
(Although it's not only 1-2-3, there is YYMMDD in the beginning and a
check digit at the end.)
> But you are assuming that I would *want* to handle people without SSNs.
Yes, in most systems where you handle persons you want to that. You set
> The point is that, either my business rule states that two people
> *cannot* share SSNs (in which case, SSN is a good candidate for a
> primary key), or the business rules state some other point of
> uniqueness,
> It simply means the business rule gets more complicated. If I was
> designing a system that was to store US and British and Aussie citizens,
> I would have an 'ID' field that could accept the US SSN, the British
> National Insurance Number (assuming it still exists!!) and the Aussie
> Tax File number.
Not all countries have any person identification numbers at all. Not all
countries even have central registers of all their citizens. Not all
countries even know how many people who lives there.
Yes, sure, there may be special cases where you can use the person
identification number as a primary key, but they are rather the exception.
Now, the point with surrogate keys is that they give you a back-door.
You can expose to the user a non-unique nullable key and give them
possibilities to work around duplicates, and spend the rest of the time
of developing the system at a reasonable cost. That may seem lax to you,
but most projects do have a budget.
--
Erland Sommarskog, SQL Server MVP, esquel_at_sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Received on Sun Aug 01 2004 - 14:17:39 CEST