Re: Table design problem

On Tue, 22 Apr 2003 08:53:02 +0000 (UTC), "Roy Hann" <rhann_at_globalnet.co.uk> wrote:

>> The names of tables and columns are really, really important.
>
>I agree that it is comforting to use names that have some mnemonic value,
>but there are no logical implications to them.

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.

>> I didn't recommend a separate party table and customer table. I
>> suggested that you consider a) parties as a supertype, b) individuals
>> as one subtype of parties, c) organizations as another subtype of
>> parties.
>
>OK, I agree you never said "create a customer table", but you did say
>"associate customers with the supertype parties". I guess I made the
>unwarranted assumption that that was somehow the essential idea you were
>proposing.

I meant that you should associate customers with the supertype "Parties" rather than treating "individual" and "organization" as subtypes of customers.

The essential thing that makes a party an individual or an organization exists independent of the essential thing that makes one a customer. 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".

When a party places an order or makes a purchase, you'd store the supertype's primary key value--the party's primary key value--with the order or purchase. In a SQL database, you'd implement a foreign key constraint that references the table of parties.

I'd also add two simplifying views: a view of persons (joining the supertype of parties with the subtype of individuals), and a view of organizations.

You might want a view of customers, too. Join the supertype of parties on distinct party primary key values from your table of orders or purchases, whatever it's called.

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

>Well once again I am struggling to be sure I am not missing your point,
>because it seems to me that if you aren't advocating introducing a new table
>for customers,

Right.

>and you are advocating a "party" table with two subtypes,

Yes.

>and
>I have to query to distinguish between parties who buy things and mere
>time-wasters,

"Time-wasters" is a peculiar way to describe prospects.

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.

-- 
Mike Sherrill
Information Management Systems