Re: Separate foreign keys with shared ID space

"Erland Sommarskog" <esquel_at_sommarskog.se> wrote in message news: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.

No, if the system is designed by someone who religiously believed in natural keys *and who didn't understand the uses to which it would be put*, then we have a problem. But that's what a design stage is for: to thrash out precisely what the uses are for the proposed system and hence what the business rules are.

Again, your point 2 is not a problem *if that is what the rules state*. I'm sure we've all heard the stories of patients without insurance being whisked out of the emergency department because the hospital administrator finds they cannot pay. Maybe illegal aliens face the same dilemma. Point is, that's a design decision: what will your system allow or not allow. And design your primary keys accordingly.

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

You've missed my point. You are assuming I would want to, and that's a valid assumption for many systems, as you state. In which case, design your keys accordingly. But there will be times when that assumption is wrong. In which case, SSNs would be a valid primary key. Point is, the key design depends upon what your system should and should not allow. What it *doesn't* depend on is sweeping statements that keys should never have business meaning.

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

I said no such thing. Quite the opposite: I said that I have no idea how SSNs work, and the details of how they work are irrelevant to the discussion in any case. What I actually said was, use SSNs if they are appropriate. Your first sentence here simply repeats what I posted earlier: it depends on the business rules (and inevitably on one's ability to understand them).

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

You are merely confirming my point. That the decision on whether natural or synthetic keys should be used depends upon a proper understanding of the business rules to be used. Which is a far cry from whoever it was who posted that 'primary keys should NEVER have a business meaning'. It doesn't sound like that was you, based on the content of this reply of yours... therefore, I think it safe to say we agree.

You are thinking of an application where duplicate or null identities should be allowed. I am thinking of an application where either of those approaches would be a big no-no. Both sorts of application exist in the real world. Both approaches are therefore valid.

HJR Received on Sun Aug 01 2004 - 07:51:57 CDT