List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:July 7 2005 6:56pm
Subject:Re: Another generalization hierarchy problem
View as plain text  
Alberto,

Persons and companies are distinctive entities, yes. Treating either as 
if it were a subspecies of the other leads one into absurdities. Either 
may be a customer, but neither need be. You haven't described the 
problem context. Conceivably one or the other could also be a 
contractor, a subcontractor, a supplier. Here's a common solution, one 
we've often used to a client's satisfaction.

Let a 'party' be any entity with which your firm does any sort of 
transaction--customers who buy something from you, contractors who do 
something for you, suppliers who sell you something, &c. Thus you likely 
need a transaction_types table eg 'customer', 'contractor', 'supplier', 
'regulator' &c).

So far, you recognise two party types, persons and companies, but others 
are easy to think of (government departments. NGOs &c). A party has a 
row in a parties table: partyID (int auto_increment), a name (char(50) 
eg 'Buffo Blair', 'ABC Cleaners', 'Inland Revenue'), and a partytype 
attribute (char(10) eg 'person', 'company', 'govt dept'&c) which refers 
to a partytypes lookup table (partytype char(10) PK).

Parties have addresses, possibly several of them, so an address table is 
the container for all address info including address type (eg 
'business', 'home', 'vacation', 'temporary place of incarceration' &c). 
Every address row has a partyID value which points at a row in parties 
to indicate whose address it is.

Persons have their special attributes ('language', 'credit card number', 
&c), so you have a persons table for all that including a partyID column 
pointing at a row in the parties table.

Likewise companies have their own special attributes, so you have a 
companies table for all that, again including a column for partyID and 
of course a column for personal contact (pointing at a persons row of 
course).

Then a customer is merely a party that buys something, so the customers 
table has columns for customer-specific info plus a column which points 
at a parties row, where it finds the customer's name, type, &c. When it 
comes time to write the app or web customer form, you hide the details 
of how to display and edit customer party info in a Customer View. 
Likewise for Address Views, Invoices and so on

PB

-----

abrea@stripped wrote:

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


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.8.10/43 - Release Date: 7/6/2005

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