Another generalization hierarchy problem
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another generalization hierarchy problem
[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
Re: Another generalization hierarchy problem
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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another generalization hierarchy problem
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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another generalization hierarchy problem
[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_customers (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
Re: Another generalization hierarchy problem
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]
Re: Another generalization hierarchy problem
Thanks to all for you help and time. After reading to replies, I think that perhaps a clean way of dealing with individual and company customers in a single table could be to leave them out of the parties tree altogether and relate them to the parties table without a generalization hierarchy, but one-to- one from a customers table. For instance: 1) PARTIES TREE: Parent: PARTIES Attr: id, type Subtypes of Parties (complete and disjoint): INDIVIDUALS Attr: id, firstname, lastname, sex, language JURENTITIES Attr: id, name, type Subtypes of Individuals (complete and overlapping): PERSONAL Attr: id, birthday BUSINESS Attr: id, title, specialization 2) CUSTOMERS TREE: Parent: CUSTOMERS Attr: partyid (references parties), taxid, billingaddress Subtypes of customers (complete and overlapping): SUBSCRIBERS Attr: partyid (references parties), startdate, enddate SERVICE1USERS Attr: partyid (references parties), xxx SERVICE2USERS Attr: partyid (references parties), xxx 3) OUTSIDE BOTH TREES: PHONES Attr: id, number, areacode (relates M-M with parties) ADDRESSES Attr: id, street,etc (relates M-M with parties) EMAILS Attr: id, email (relates M-M with parties) URLS Attr: id, url (relates M-M with parties) The three would need an associative table separating them from the parties table, with a composite key Am I going wrong somewhere? Alberto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another generalization hierarchy problem
Thanks to all for you help and time. After reading to replies, I think that perhaps a clean way of dealing with individual and company customers in a single table could be to leave them out of the parties tree altogether and relate them to the parties table without a generalization hierarchy, but one-to- one from a customers table. For instance: 1) PARTIES TREE: Parent: PARTIES Attr: id, type Subtypes of Parties (complete and disjoint): INDIVIDUALS Attr: id, firstname, lastname, sex, language JURENTITIES Attr: id, name, type Subtypes of Individuals (complete and overlapping): PERSONAL Attr: id, birthday BUSINESS Attr: id, title, specialization 2) CUSTOMERS TREE: Parent: CUSTOMERS Attr: partyid (references parties), taxid, billingaddress Subtypes of customers (complete and overlapping): SUBSCRIBERS Attr: partyid (references parties), startdate, enddate SERVICE1USERS Attr: partyid (references parties), xxx SERVICE2USERS Attr: partyid (references parties), xxx 3) OUTSIDE BOTH TREES: PHONES Attr: id, number, areacode (relates M-M with parties) ADDRESSES Attr: id, street,etc (relates M-M with parties) EMAILS Attr: id, email (relates M-M with parties) URLS Attr: id, url (relates M-M with parties) The three would need an associative table separating them from the parties table, with a composite key Am I going wrong somewhere? Alberto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
