Re: Eliminating Combinatorial Relationship Multiplication

> Separate the tree structure from the nodes. Ilike the nested sets
> model for the structure, but you can pick whatever works best for your
> situation. Then the nodes can go into another table.
>
> The classic scenario calls for a root class with all the common
> attributes and then specialized sub-classes under it.

The problem I described was not so much with modelling the tree (thanks partly to tips from your book which I do own) but with 2 entities having semantically identical relationships with N other entities. In my case the 2 entities are nodes and leaves - organizations and users.

For simplicity of the example I'll use the path enumeration model (I know nested set is better).

create table organizations (int id, varchar name ... etc) create table users (int id, varchar name ... etc) create table orgtree (varchar path, int orgId) create table members (int orgId, int userId) // which organizations a user belongs to

create table partners  (int id, ... etc)
create table purchases (int id, ... etc)
create table customers (int id, ... etc)

The problem is that both users and organizations can have relationships with partners, purchases, and customers. Thus I have to have 2*N = 6 link tables to represent that. This example is simplified, in reality I have about 12 entities with which both organizations and users can have relationships so I have to have 24 link tables. Nothing wrong with that per se but I just think it is inelegant.

In OO technology there are standard solutions for this kind of thing but in SQL the only approach I could think of is to introduce an "intermediate" entity (say it's called "holder") which will hold the references to the relationships that both a user and an organization can have. Users and Organizations can then in turn have a relationship with this "holder" entity. Thus the number of link tables will be N + 2 or 14 instead of 24.

My question is: Are there any other approaches to this problem? Seems like a fairly common issue.

Thanks!

Jeff

P.S. CELKO, I have to deal with tree stuff in SQL a lot and I originally I bought your book simply because there was nothing else on the topic and I looked hard both recently and in the past! I did not have high expectations because I thought it was just a quickie to capitalize on success of "SQL for smarties" but I was pleasently surprised: as the title suggests, it is definitely the most comprehensive compilation of SQL techiques for modelling trees in relational structures all gathered in one place that I ever saw. Well worth the price,thanks for putting it together!

jcelko212_at_earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0407010727.4fbb407d_at_posting.google.com>...
> Here is the link on Amazon.com for my new book on "Trees & Hierarchies
> in SQL"
>
> http://www.amazon.com/exec/obidos/tg/detail/-/1558609202/qid=1080772873/sr=1-1/ref=sr_1_1/102-7683601-6345721?v=glance&s=books#product-details
>
> Separate the tree structure from the nodes. Ilike the nested sets
> model for the structure, but you can pick whatever works best for your
> situation. Then the nodes can go into another table.
>
> The classic scenario calls for a root class with all the common
> attributes and then specialized sub-classes under it. As an example,
> let's take the class of Vehicles and find an industry standard
> identifier (VIN), and add two mutually exclusive sub-classes, Sport
> utility vehicles and sedans ('SUV', 'SED').
>
> CREATE TABLE Vehicles
> (vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) NOT NULL
> CHECK(vehicle_type IN ('SUV', 'SED')),
> UNIQUE (vin, vehicle_type),
> ..);
>
> Notice the overlapping candidate keys. I then use a compound candidate
> key (vin, vehicle_type) and a constraint in each sub-class table to
> assure that the vehicle_type is locked and agrees with the Vehicles
> table. Add some DRI actions and you are done:
>
> CREATE TABLE SUV
> (vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) DEFAULT 'SUV' NOT NULL
> CHECK(vehicle_type = 'SUV'),
> UNIQUE (vin, vehicle_type),
> FOREIGN KEY (vin, vehicle_type)
> REFERENCES Vehicles(vin, vehicle_type)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> ..);
>
> CREATE TABLE Sedans
> (vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
> CHECK(vehicle_type = 'SED'),
> UNIQUE (vin, vehicle_type),
> FOREIGN KEY (vin, vehicle_type)
> REFERENCES Vehicles(vin, vehicle_type)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> ..);
>
> I can continue to build a hierarchy like this. For example, if I had
> a Sedans table that broke down into two-door and four-door sedans, I
> could a schema like this:
>
> CREATE TABLE Sedans
> (vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
> CHECK(vehicle_type IN ('2DR', '4DR', 'SED')),
> UNIQUE (vin, vehicle_type),
> FOREIGN KEY (vin, vehicle_type)
> REFERENCES Vehicles(vin, vehicle_type)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> ..);
>
> CREATE TABLE TwoDoor
> (vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) DEFAULT '2DR' NOT NULL
> CHECK(vehicle_type = '2DR'),
> UNIQUE (vin, vehicle_type),
> FOREIGN KEY (vin, vehicle_type)
> REFERENCES Sedans(vin, vehicle_type)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> ..);
>
> CREATE TABLE FourDoor
> (vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) DEFAULT '4DR' NOT NULL
> CHECK(vehicle_type = '4DR'),
> UNIQUE (vin, vehicle_type),
> FOREIGN KEY (vin, vehicle_type)
> REFERENCES Sedans (vin, vehicle_type)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> ..);
>
> The idea is to build a chain of identifiers and types in a UNIQUE()
> constraint that go up the tree when you use a REFERENCES constraint.
> Obviously, you can do variants of this trick to get different class
> structures.
>
> If an entity doesn't have to be exclusively one subtype, you play with
> the root of the class hierarchy:
>
> CREATE TABLE Vehicles
> (vin CHAR(17) NOT NULL,
> vehicle_type CHAR(3) NOT NULL
> CHECK(vehicle_type IN ('SUV', 'SED')),
> PRIMARY KEY (vin, vehicle_type),
> ..);
>
> Now start hiding all this stuff in VIEWs immediately and add an
> INSTEAD OF trigger to those VIEWs.
Received on Thu Jul 01 2004 - 19:10:39 CDT