Eliminating Combinatorial Relationship Multiplication
Date: 30 Jun 2004 13:20:07 -0700
Message-ID: <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? Received on Wed Jun 30 2004 - 22:20:07 CEST