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