Re: Table design problem

"Mike Sherrill" <MSherrill_at_compuserve.com> wrote in message news:sgv1bv08kr5huhmqdhnaoehiisht1ku4pe_at_4ax.com...
> There are semantic implications to them. If you want to argue that
> semantic implications carry no logical weight, start a new thread. A
> new thread will attact more opinions.

On the contrary, I'm confident that semantic implications do carry logical weight, but names are of purely psychological (and possibly ergonomic) value, nothing more. Relational theory has nothing to say about encoding semantics in names, and for good reason. Anyway, our names happen to correspond with the terms used by the business and give us a common language for communicating with the users. There is no value in changing the names to solve a problem we don't have.

> The essential thing that makes a party an individual or an
> organization exists independent of the essential thing that makes one
> a customer.

Absolutely.

> The essential thing that makes one a customer does *not*
> exist independent of a purchase or order. Not in the sense that most
> of us use the word "customer".

How you use the word is your business. How someone else uses the word is their business. How we use the word is our business. Without understanding the local business processes and the local business "culture" you are being wildly presumptuous. Indeed that is a very excellent reason *not* to rely on names to guess at semantic implications. You simply aren't entitled to do it.

Note that I am not saying your suggestion of a view that materializes "customers" is wrong. It's not, and I guess it quite neatly reflects your use of the term "customer". But that *is* a guess on my part. All I can rely on is what I see in your view definition--I can certainly know what that means (assuming I know what the underlying tables mean), and I don't need to rely on names, though of course I'm pleased when they give me a useful hint in the right direction.

> >Yeah, kindof. I couldn't see any way out of appending a discriminator to
> >the primary key value and I was hoping I was wrong, but that's the only
> >suggestion I got. It'll do the job.
>
> The discriminator doesn't need to be part of a key. It just can't be
> null.
>
> Without the discriminator, a CHECK constraint can guarantee that
> there's a matching row in at least one of the subtype tables, but it
> can't guarantee that there's a matching row in the *right* subtype
> table. That's why you need it.

Turning it around, what would be the disadvantage of encoding the discriminator in the primary key? I can't see any, and I can see a couple of advantages to doing so. First of all I could never create a projection of the table that preserves the key but lacks the discriminator. Secondly, if my subtypes have further subtypes (e.g. non-profit corporations are a subtype of corporations, which are a subtype of parties), then I can tell without reference to the supertype table what further subtypes I should expect based on an inspection of a row in any given subtype. (I could get around that by insisting on having the discriminator as an attribute of every subtype, but then it might as well be part of the key.) Thirdly I could add a new subtype that further specializes one of my existing subtypes without having to add a column to it, and more constraints.

[snip]
> >I have to query to distinguish between parties who buy things and mere
> >time-wasters,
>
> "Time-wasters" is a peculiar way to describe prospects.

I was being jocular. If your observation about the term is not, then it is an example of why it is an error to read meaning into names, because there wasn't any in this case.

> If there aren't any attributes peculiar to the relationship between a
> business and its customers, you don't need a table for them. Whavever
> those attributes might be, you'd need to make a case that they're not
> really attributes of a party, an individual, an organization, a
> purchase, or an order. Because you already have tables for those,
> even if they're misnamed.

If we can agree that they might be named *differently*, then we can agree on everything. But if you insist that I agree that misnaming is even a meaningful concept, logically, then sadly we won't agree at all.

Roy Received on Tue May 06 2003 - 13:11:48 CEST