Re: Stupid Database Tricks
Date: Thu, 14 Jun 2007 13:33:19 GMT
Message-ID: <zsbci.33184$Um6.27703_at_newssvr12.news.prodigy.net>
"Marshall" <marshall.spight_at_gmail.com> wrote in message
news:1181578386.358359.258320_at_z28g2000prd.googlegroups.com...
> On Jun 11, 1:40 am, "Brian Selzer" <b..._at_selzer-software.com> wrote:
>> "Keith H Duggar" <dug..._at_alum.mit.edu> wrote in > > Brian Selzer wrote: >> >> Marshall wrote: >> >> > If the table has only two columns, as I specified, then it >> >> > [value1,value2] is necessarily unique, by the definition of >> >> > relation. Even it it wasn't, as may be the case in badly >> >> > executed SQL tables, there is still no value in adding an >> >> > additional column which will contain no further information. >> >> >> The problem is not that the key values aren't unique within a single >> >> extension of the database, it is that a key value may identify one >> >> object >> >> in >> >> the universe in one extension and a different object in another. A >> >> surrogate key solves this problem because there is a bijective mapping >> >> between the values in the surrogate domain and all possible objects in >> >> the >> >> universe of discourse. >> >> > There is no such "object" as an "object". If you stop thinking >> > in terms of "objects" and "entities" then you will stop having >> > the fake problems which lure you to the surrogate ID crutch. >> >> I would have to stop thinking altogether: without objects there can be no >> conception; without objects there can be no perception; without objects >> there can be no discourse! >
> Well, that seems a bit overstated. I think abstract algebra shows
> us that it's possible to work usefully with a formal system even
> without having a specific model in mind.
>
> I'm unclear exactly how to read your use of the word "extension"
> above.
> Do you mean the semantic connection with the universe of discourse?
> Or a specific database value or instance? Hair extensions? (j/k)
> Probably a more specific, less ambiguous term would be better.
>
I guess "extension" isn't the right word, although Codd used it in this sense, and it does represent a set of propositions that are true under a given interpretation for a particular state of affairs. An extension in this sense is a set of relations that satisfies all of the state constraints specified in the schema of a database. Database instance or state or value would therefore be synonymous.
The schema of a database describes the set of all possible database values; whereas the body specifies which of the possible database values is actual, and indirectly, due to the closed world and domain closure assumptions, which objects in the universe actually exist. So, under a given interpretation, the schema of a database specifies what *can be* true, whereas the body--the actual database value--represents what *is* true. Note also that there can only be one database value that represents what *is* true without introducing a contradiction.
The need for surrogates boils down to this:
possibly A and possibly B does not imply possibly (A and B).
The schema of a database is a modal predicate. The extension of that predicate is a set of positive modal formulae. The problem is that the presence of a compound key forces the modal predicate to be second-order, since it must range over all possible objects, including those identified by a compound key value. So, the extension of such a predicate is not a set of propositions, but a set of predicates, and the extensions of those predicates may intersect. So unless
possibly A and possibly B implies possibly (A and B),
a compound key value (A, B) may identify a different object at different possible database values. It follows then that different compound key values may identify the same object at different possible database values.
The use of surrogate keys ensures that the modal predicate is first-order, eliminating any ambiguity.
> It also seems you're suggesting that surrogate key should
> be *globally* unique. Do you mean that? If so, yuck.
>
I'm not sure what you mean by *globally* unique. A surrogate key value should permanently identify an object in the universe. All surrogate keys should be drawn from the same domain. If that equates to *globally* unique, then that's what I mean.
> >> (Perhaps I should have used the term "individual" instead of "object" to >> avoid confusion.) >
> I don't see how one could improve on "proposition" and "predicate."
>
Neither a proposition nor a predicate can stand on its own. Without an interpretation, each is meaningless.
>
> Marshall
>
Received on Thu Jun 14 2007 - 15:33:19 CEST