[EMAIL PROTECTED] 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
> [EMAIL PROTECTED]
>
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
