List:General Discussion« Previous MessageNext Message »
From:SGreen Date:July 7 2005 6:36pm
Subject:Re: Another generalization hierarchy problem
View as plain text  
abrea@stripped wrote on 07/07/2005 01:57:33 PM:

> Dear Shawn,
> Thanks for your reply.
> I find that "individuals" and "companies" each have attributes that 
> are completely irrelevant to the other. E.g. "individuals" have sex 
> and language (so e-mail can be sent to them as "Dear Sir" or "Dear 
> Madam" in both English and Spanish). The only time they have 
> information in common is when they are customers, where they have a 
> tax id, billing address, sales, etc.
> 
> By "company" I mean any juridical entity (I didn't use the 
> word "entity" in order not to confuse it with ERD entities). So 
> a "company" can have many "individuals" and an individual can also 
> have multiple "companies" (e.g. the firm he works for, a club, a 
> professional association).
> 
> An "individual" can be of the subtype "personal_relation" 
> or "customer" but for some persons both types overlap (e.g. a 
> personal friend with whom I also do business).
> 
> I think that I cannot put 'customer' as the supertype because 
> many 'individuals' and 'companies' are not customers (I wish they 
> were :-)) so they wouldn't share the 'customer' attributes.
> 
> ATTEMPT: I thought of having an "individual_customer" and 
> an "company_customer" as subtypes of "individual" and "company", 
> respectively.
> But in your experience, wouldn't it be a mess to have half of the 
> customers in one entity and half in the other?
> 
> Best regards,
> Alberto Brea
> estudio_brea@stripped
> 

Not really... You have your actual customer information in two places 
(individual and company) already. Creating two kinds of "customer" 
relationships actually makes some sense. Sure you have two customer tables 
but you can make them seem like one if you can create a view (v 5.0+) or 
use a merge table (MyISAM tables only) or use a UNION query (v4.0+)

Having them split into two tables will simplify certain queries (show me 
all corporate customers). You could also add additional customer relation 
information to the "company_customer" table so that you can keep more 
details about them than you do "individual_customer"s (most businesses 
want to turn big customers into bigger customers. This extra information 
could assist with that)

Just so that we are on the same page...I think this is roughly where you 
are headed. All "properties" are merely ideas and not suggestions:

--Objects--
        Customer - the purchaser of at least one Order of goods or 
services
        Company - an organization composed of one or more Individuals
        Individual - a person that may or may not be part of an Company
        Customer_Company - the details of the business relationship 
between you and a Company that is also a Customer
        Customer_Individual - the details of the business relationship 
between you and an Individual that is also a Customer
        Order - The sale of one or more goods or services to a customer

--Property lists--
Customer: ID, date of first order, date of last order, # of orders placed, 
total value ordered
Company: ID, name, billing address, shipping address
Individual: ID, name(s), billing address, shipping address
Customer_Company (details about the relationship of a company AS a 
customer): ID, Customer_id, Company_id, Contact Histories (list), 
Status,...
Customer_Individual (details about the relationship of an individual AS a 
customer): ID, Customer_id, Individual_id, Contact Histories(list), 
Status,...

The Contact Histories (I couldn't think of a better term right off the top 
of my head) would be the records of correspondence (sales letters, 
billing, faxes, ...) and phone calls between you and your customers. I 
guess they should probably attach to the Company and Individual objects 
that way you can record pre-sales and post-departure contact information, 
too. However, I think you can see that I treat the relationship *itself* 
as an object that has a life of it's own.

So far, I really like your design.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
Another generalization hierarchy problemabrea7 Jul
Re: Another generalization hierarchy problemSGreen7 Jul
Re: Another generalization hierarchy problemabrea7 Jul
Re: Another generalization hierarchy problemabrea7 Jul
Re: Another generalization hierarchy problemSGreen7 Jul
Re: Another generalization hierarchy problemPeter Brawley7 Jul
Re: Another generalization hierarchy problemabrea7 Jul
Re: Another generalization hierarchy problemabrea7 Jul