Re: Surrogate keys (Was: enums)

Em Qui, 2006-01-19 às 22:29 +0100, Martijn van Oosterhout escreveu:
> Possibly nowhere. But when you send invoices to customers, any details
> on there *are* immutable. Sure, in your database you don't care if
> things change, but then they don't match reality anymore do they?

Then what you need is a temporal database -- at least some form of
historical records. Nothing to do with keys in themselves.

> I never said there were duplicate tuples, just that the data has no
> natural keys. The tuples are unique because there's a surrogate key.

This does not guarantee uniqueness, as the key is artificially and
internally generated.

> It
> is entirely possible to have two people with the same first name, last
> name and date of birth. Rather uncommon, but the database must be able
> to support it.

And the way to support it is to take into account additional data --
place of birth, parents' data etc -- as part of the candidate keys. Not
to allow duplicates.

> I don't understand your example though. If you have a personnel
> directory with two rows with the same first and last name, what does
> that tell you. Nothing. You have to go find out whether there really
> are two of those people or not.

And how will you do that if you don't store additional data?

> You can simplify the process by taking
> into account the fact that it's very unlikely, but a unique constraint
> is not the answer.

Oh yes, it is. They only one.

> Besides, it's far more likely the same person will
> appear twice with two different spellings of their name. :)

So what?

--
+55 (11) 5685 2219 xmpp:leandrod(at)jabber(dot)org
+55 (11) 9406 7191 Yahoo!: lgcdutra
+55 (11) 5686 9607 MSN: leandro(at)dutra(dot)fastmail(dot)fm
+55 (11) 4390 5383 ICQ/AIM: 61287803