Re: Table design problem

On Thu, 17 Apr 2003 10:14:25 +0000 (UTC), "Roy Hann" <rhann_at_globalnet.co.uk> wrote:

>OK, I think I see what you are getting at here. I think you are suggesting
>that my model ought to allow me to record the existence of a party
>regardless of whether they place an order (making them a customer).

Yes, in part.

>If I am
>correct about this, then I think you really just object (perhaps properly)
>to my table name being "customer". I never said so originally, but my model
>always did permit what I called a customer without any corresponding order.

Well, where I'm from, a customer is someone who has either bought or ordered a product from me. If they haven't bought or ordered from me, they're not a customer. Now, they might be a prospect. They might be a neighbor. They might be a vendor. But they're not a customer.

A big part of database design is figuring out what you're talking about. Another big part is figuring out what you need to say about what you're talking about.

The names of tables and columns are really, really important.

>Furthermore, there are no attributes in the join of the new customer table
>and the new party table that would not also exist in my old customer table,
>so a separate party table and customer table don't appear to add any value.
>Indeed it appears spurious.

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. You need several constraints to make sure your data is sane; I presume you've nailed those down?

Your customers are just parties who have bought or ordered from you--most applications can simply use a query for that.

You'd use a table for customers--not a query--if you've identified attributes of the customer relationship that aren't properly attributes of any other table. I can't think of any off the top of my head.

>Or do I continue to miss the point?

I think so, but I'm not sure.

-- 
Mike Sherrill
Information Management Systems