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',
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
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
>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
>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",
>But in your experience, wouldn't it be a mess to have half of the
>customers in one entity and half in the other?
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