Re: Separate foreign keys with shared ID space

From: Erland Sommarskog <esquel_at_sommarskog.se>
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:
> "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.

Two cases:

1. You're a stock broker, and you need to enter person-numbers for natural persons that pays taxes in your own country, since the tax authorities requires you to report their transactions and positions. But for customers that pays tax elsewhere, or are juridical persons you have no requirement. And there might be some shady customers which you should report, but which prefer that you don't.
2. You are entering data about patients at a hospital. And here am I, an illegal alien, or just an unfortunate tourist, and you cannot enter me.

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 up a web shop. You only accept customers that have an Australian SSN?

> 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,

Or the business rules does not state any sort of 100% uniqueness. Business rules comes from people acting, and people are not computers. They don't need any 100% unique key. If a way of identification, like SSN is 99% unique, that's good enough for them, and they few duplicates can be handled informally. But an RDBMS is not equally flexible.

Sure, you can bend over backwards and find something which makes each instance unique, but how much extra value does that give the organization who is paying for the work, and if you did not bend long enough, you may still have left them in a dead end.

Understanding business rules and business requirements is essential when you design a database. It's easy to say "use SSN and not surrogate keys", as long you are naïve to believe SSN:s to be unqiue and non nullable.

> 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