Re: Eliminating Combinatorial Relationship Multiplication

"Jeff Lanfield" <jlanfield2003_at_yahoo.com> wrote in message news:235c483f.0406301220.1e41d7c4_at_posting.google.com...
> Suppose I have users that can belong to organizations. Organizations
> are arranged in a tree. Each organization has only one parent
> organization but a user maybe a member of multiple organizations.
>
> The problem that I'm facing that both organizations and individual
> users may have relationships with other entities which are
> semantically the same. For instance, an individual user can purchase
> things and so can an organization. An individual user can have
> business partners and so can an organization. So it seems that I would
> need to have a duplicate set of link tables that link a user to a
> purchase and then a parallel link table linking an organization to a
> purchase. If I have N entities with which both users and organizations
> may have relationships then I need 2*N link tables. There is nothing
> wrong with that per se but just not elegant to have two different
> tables for a relationship which is the same in nature, e.g.
> purchaser->purchaseditem.
>
> One other approach I was thinking of is to create an intermediate
> entity (say it's called "holder") that will be used to hold references
> to all the relationships that both an organization and an individual
> may have. There will be 2 link tables linking organizations to
> "holder" and users to "holder". Holder will in turn reference the
> purchases, partners and so on. In this case the number of link tables
> will be N+2 as opposed to 2*N but it will have a performance cost of
> an extra join.
>
> Is there a better way of modelling this notion of 2 different entities
> that can possess similar relationships with N other entities?

You need to convert the following into an ERD. I've supplied particpation (may) constraints and cardinality constraints. Once the ERD is done, it is a snap to convert to a relational schema.

PEOPLE n (may) have m BUSINESS_PARTNERS
ORGANIZATIONS n (may) have BUSINESS_PARTNERS PEOPLE n (may) belong_to m ORGANIZATIONS PEOPLE 1 (may) buy n GOODS
ORGANIZATIONS 1 (may) buy n GOODS

So, in mock pseudo ERD form:

PEOPLE == >have m:n ==> BUSINESS_PARTNERS <== n:m have <== ORGANIZATIONS

     "         ==> people_order ==>   1:m            GOODS      m:1
<== orgs_order <==             "

So, you wind up with

PEOPLE
person_id PK
etc

ORGANIZATIONS
org_id PK
etc

BUSINESS_PARTNERS
person_id PK (FK)
org_id PK (FK)

GOODS
item_id (PK)
description
etc

PEOPLE_ORDER
order_id PK
person_id (FK)
item_id (FK)
order_date
etc

You can now either create an ORGS_ORDER table, or add in org_id to the PEOPLE_ORDER table, and change the table name to ORDER. This is a decsion based on many things, primarily the semantics of person_id and org_id. If they are mutually exclusive, then you can combine the tables simply by adding org_id. If they are not mutually exclusive, a horrible option is to add a flag to the ORDERS table, indicating whether the order is from a person or an organization. Don't do it. Received on Wed Jun 30 2004 - 20:29:07 CDT