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
