Re: Surrogate keys (Was: enums)

[Leandro Guimarães Faria Corcete Dutra <leandro(at)dutra(dot)fastmail(dot)fm>]

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