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]
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]
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]
