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.

This is common scenario for a Customer, for example, where the Customer can then be either an organization or an individual.

> Is there a better way of modelling this notion of 2 different entities
> that can possess similar relationships with N other entities?

If you are just looking to model it, then using an interface/implementation approach would work where you have Customer (Holder) as an interface and Organization and Person both implementing that interface. You could toss in an abstract class (partial implementation of the Interface) for the relationship implementations and extend that too. This is more handily modeled in UML OO types of diagrams than with relations, it seems to me.

But, if you are looking to model it in order to implement in a SQL database then I think you are headed down the right path with your Holder pattern, although there could be other approaches that I'm missing too. I'd suggest putting some work into picking a meaningful name for the Holder relation (for example, it makes complete sense to everyone that a Customer could be a person or an org).

Cheers! --dawn Received on Wed Jun 30 2004 - 16:21:35 CDT