List:General Discussion« Previous MessageNext Message »
From:SGreen Date:July 7 2005 3:41pm
Subject:Re: Another generalization hierarchy problem
View as plain text  
abrea@stripped wrote on 07/07/2005 11:22:52 AM:

> Dear list:
> I am preparing an entity relationship diagram and encountered the 
> following problem:
> The entity "individual" has the attributes firstname, lastname.
> The entity "company" has the attributes name, companytype.
> Now I wish to include the subtype "customer" with the attributes 
> taxid, billingaddress. However, a customer may be either an 
> individual or a company, and I understand that a subtype can only 
> have one supertype.
> (I cannot put "customer" as the supertype because there are many 
> individuals and companies that are not customers.)
> 
> How can I implement the subentity "customer" in my ERD?
> Can I do it at all?
> Thank you
> 
> Alberto Brea
> 

What would be wrong with using the same information for "company" as you 
have for "individual" (a "company" of one). Technically (even though they 
are the same physical being) they are two different logical entities in 
relationship to your system. If you define a "customer" as a business 
entity, "company", with which you have some relationship then people need 
to impersonate companies in order to form that relationship. "Individuals" 
are distinguished by the fact that they are members of a "company" and you 
do not have direct business with them but rather with their parent entity, 
the "company" by way of the "customer" object. For the case of direct 
consumer purchases, the "company" and "individual" records would be 
identical.

Another option is to have "customer" become the supertype and you derive 
two subtypes "company" and "individual". The qualification would be at the 
"customer" level of whether they are a current, future (contact), or past 
customer...  It's a change of perspective and focuses on the business 
relationship and the entities that share that relationship rather than the 
entities and what relationship you have with them.

Last idea: Expand your idea of "company" and "individual" so that they fit 
on the same table "entity" (or whatever works in your naming model. Then 
you could use an attribute on the table to distinguish between public and 
corporate customers.  I like this idea least as it muddles two distinct 
entities into one.

So my suggestions summed up:
a) Make "individuals" also their own "companies"
b) Modify your entity diagram so that both "companies" and "individuals" 
become subtypes of "customer"
c) Change your design so that "companies" and "individuals" are subtypes 
of a common "entity". Create your customer relationships to that common 
parent.

I am sure there will be dozens of other suggestions coming in from others 
on the list...(hint hint!)

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