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
-----
[EMAIL PROTECTED] 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
[EMAIL PROTECTED]
--
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
