Eliminating Combinatorial Relationship Multiplication

[Jeff Lanfield]

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->purchasedite m.

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?

[Daniel Morgan]

--CELKO-- wrote:

Here is the link on Amazon.com for my new book on "Trees & Hierarchies
in SQL"

http://www.amazon.com/exec/obidos/tg...roduct-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_t ype 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_t ype = '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_t ype = '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_t ype 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_t ype = '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_t ype = '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_t ype 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.

Joe ... sorry ... but integrity demands that I write the following:

We have a usenet group named comp.databases. oracle.marketpl ace
specifically designated for promotions. In the future it would be
appreciated if you posted book, or any other, promotions there.

Thanks.

For everyone else ... I recommend Joe's books to my students and
highly recommend them.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Marshall Spight]

"Daniel Morgan" <da******@x.was hington.edu> wrote in message news:1088831855 .922891@yasure. ..

Joe ... sorry ... but integrity demands that I write the following:

We have a usenet group named comp.databases. oracle.marketpl ace
specifically designated for promotions. In the future it would be
appreciated if you posted book, or any other, promotions there.

Uh, so a guy posts two lines with a link where you can buy his
book, and 100 lines of helping someone out with his question,
and you bust his balls for "promotions ?" You also assume
that because the original poster included an oracle newsgroup
in one of the four newsgroups he posted to, that everyone responding
on any of those newsgroups should somehow know your local
culture and customs and adhere to them?

You have a funny idea of integrity.
Marshall

[Daniel Morgan]

Marshall Spight wrote:

"Daniel Morgan" <da******@x.was hington.edu> wrote in message news:1088831855 .922891@yasure. ..

Joe ... sorry ... but integrity demands that I write the following:

We have a usenet group named comp.databases. oracle.marketpl ace
specificall y designated for promotions. In the future it would be
appreciated if you posted book, or any other, promotions there.

Uh, so a guy posts two lines with a link where you can buy his
book, and 100 lines of helping someone out with his question,
and you bust his balls for "promotions ?" You also assume
that because the original poster included an oracle newsgroup
in one of the four newsgroups he posted to, that everyone responding
on any of those newsgroups should somehow know your local
culture and customs and adhere to them?

You have a funny idea of integrity.
Marshall

I don't show favoritism when it come to calling spam spam. Even when it
is someone as esteemed as Joe Celko.

But exactly what is it you have contributed to c.d.o.server?

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Gene Wirchenko]

Daniel Morgan <da******@x.was hington.edu> wrote:

Marshall Spight wrote:

"Daniel Morgan" <da******@x.was hington.edu> wrote in message news:1088831855 .922891@yasure. ..

Joe ... sorry ... but integrity demands that I write the following:

We have a usenet group named comp.databases. oracle.marketpl ace
specifical ly designated for promotions. In the future it would be
appreciate d if you posted book, or any other, promotions there.

Uh, so a guy posts two lines with a link where you can buy his
book, and 100 lines of helping someone out with his question,
and you bust his balls for "promotions ?" You also assume
that because the original poster included an oracle newsgroup
in one of the four newsgroups he posted to, that everyone responding
on any of those newsgroups should somehow know your local
culture and customs and adhere to them?

You have a funny idea of integrity.

I don't show favoritism when it come to calling spam spam. Even when it
is someone as esteemed as Joe Celko.
What spam? OP asked for help, and Mr. Celko gave it.
But exactly what is it you have contributed to c.d.o.server?

Possibly a reminder of priorities? You may have missed this point
though.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

[Marshall Spight]

"Daniel Morgan" <da******@x.was hington.edu> wrote in message news:1088887202 .344807@yasure. ..

I don't show favoritism when it come to calling spam spam. Even when it
is someone as esteemed as Joe Celko.
Spam is by definition unsolicited. An on-topic, helpful response to a
usenet post requesting help is by definition solicited. Your "integrity"
is just ball-busting on a guy who bends over backwards to help all
comers for free, and also happens to sell some books for which
he's probably lucky to clear $25,000 annually. (Trees and
Hierarchies in SQL doesn't have the same cachet as the latest
work by David Sedaris. In fact, it's Amazon's 16,754's most
popular item, right behind the $350 "Suunto Yachtsman Wristop
Computer Watch w/ Barometer and Compass", and you can
bet a $350 wrist barometer isn't exactly flying off the shelves.)

You're not engaging in spam-vigilance; you're just engaging
in gratuitous anticommerciali sm.

But exactly what is it you have contributed to c.d.o.server?

I could just as well ask what you've contributed to c.d.theory,
but that wasn't the point. The point was that this thread isn't
specific to your newsgroup; it spans multiple newsgroups.
Expecting someone on this thread to conform to the customs
of one of those newsgroups is as unrealistic as those folks in
Snakewater, Iowa, who get upset when they see something
that violates a local ordinance posted on the internet from
New York City.

But the absolute frosting on the cake, the hypocritical parsley
on the potatoes, is the fact that both of your posts on this
topic so far have included links to courses you teach at
Washington. You're giving those courses away for free,
I presume? Oh, no, wait, I see; Oracle Application Development
is $1875. At least Joe's links to his book on Amazon were
pertinent to the OP's question.
Marshall

[omlet]

Daniel Morgan <da******@x.was hington.edu> wrote in message news:<108883185 5.922891@yasure >...

--CELKO-- wrote:

Here is the link on Amazon.com for my new book on "Trees & Hierarchies
in SQL"

http://www.amazon.com/exec/obidos/tg...roduct-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_t ype 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_t ype = '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_t ype = '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_t ype 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_t ype = '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_t ype = '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_t ype 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.

Joe ... sorry ... but integrity demands that I write the following:

We have a usenet group named comp.databases. oracle.marketpl ace
specifically designated for promotions. In the future it would be
appreciated if you posted book, or any other, promotions there.

Thanks.

For everyone else ... I recommend Joe's books to my students and
highly recommend them.

Daniel Morgan: Cannot you just shut the ffff up? Take your
contributions and shove it! This an international newsgroup and it NOT
yours to guard. The charter of this newsgroup is clear and does not
allow you to add shameless lines of advertisements while denying
others!

So shut up! you can create your own newsgroup where you can control
things --- you crazy control freak. Watch the movie "Tow Truck" to see
what you look like.

Obviously you are downgrading this group and many others to your level
of integrity which you completely lack.

Remember, you big jumble of Q that Oracle itself is a product! and you
have no rights what so ever to determine what is good; and what is
like you of what you don't like. You get it?! Read Simon's peom! Or
download OMLET and run it and you would get random lines of insults
and images geared specifically to YOU! Till 2010.

Now what?!
Right on your tail.

[Noons]

om***@omlet.org apparently said,on my timestamp of 5/07/2004 8:27 AM:

Daniel Morgan: Cannot you just shut the ffff up? Take your
contributions and shove it! This an international newsgroup and it NOT
yours to guard. The charter of this newsgroup is clear and does not
allow you to add shameless lines of advertisements while denying
others!

So shut up! you can create your own newsgroup where you can control
things --- you crazy control freak. Watch the movie "Tow Truck" to see
what you look like.

Obviously you are downgrading this group and many others to your level
of integrity which you completely lack.

Remember, you big jumble of Q that Oracle itself is a product! and you
have no rights what so ever to determine what is good; and what is
like you of what you don't like. You get it?! Read Simon's peom! Or
download OMLET and run it and you would get random lines of insults
and images geared specifically to YOU! Till 2010.

Now what?!
Right on your tail.

forwarded to abuse.

--
Cheers
Nuno Souto
wi*******@yahoo .com.au.nospam

[Daniel Morgan]

Noons wrote:

forwarded to abuse.

I have too as have a number of my students.

Here's what we have found:

Pinging omlet.org returns 66.218.79.158 which in return, when pinged,
returns p2w2.geo.scd.ya hoo.com.

Complaints have been filed with:
ab***@yahoo.com
gr**********@go ogle.com

I encourage others to do so too and and in case anyone wonders who this
spam-troll really is ...

Domain Name:OMLET.ORG
Created On:15-Jun-2004 10:54:38 UTC ... (been in business only 2 weeks)
Sponsoring Registrar:R52-LROR
Registrant Name:Amjad Daoud
Registrant Street1:Queen Noor Br 07
Registrant City:Amman
Registrant State/Province:Amman
Registrant Postal Code:9626
Registrant Country:JO ... (this is the country of Jordan)
Registrant Phone:[phone]
Registrant Email:te******* ***********@yah oo.com

Yes our troll lives in the country of Jordan. You can purchase a
product from someone to use with your valuable Oracle database
where there is not only no company behind it there is just one
guy in a third-world country.

Please address complaints about his spamming and abuse to:
Tech Name:YahooDomai ns TechContact
Tech Organization:Ya hoo! Inc
Tech Street1:701 First Ave.
Tech City:Sunnyvale
Tech State/Province:CA
Tech Postal Code:94089
Tech Country:US
Tech Phone:[phone]
Tech Email:do******* **@YAHOO-INC.COM
Name Server:YNS1.YAH OO.COM
Name Server:YNS2.YAH OO.COM

Thank you.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[x]

**** Post for FREE via your newsreader at post.usenet.com ****
"Daniel Morgan" <da******@x.was hington.edu> wrote in message
news:1089057907 .319050@yasure. ..

Here's what we have found: I encourage others to do so too and and in case anyone wonders who this
spam-troll really is ... Domain Name:OMLET.ORG
Created On:15-Jun-2004 10:54:38 UTC ... (been in business only 2 weeks)
Sponsoring Registrar:R52-LROR
Registrant Name:Amjad Daoud
Registrant Street1:Queen Noor Br 07
Registrant City:Amman
Registrant State/Province:Amman
Registrant Postal Code:9626
Registrant Country:JO ... (this is the country of Jordan)
Registrant Phone:[phone]
Registrant Email:te******* ***********@yah oo.com Yes our troll lives in the country of Jordan. You can purchase a
product from someone to use with your valuable Oracle database
where there is not only no company behind it there is just one
guy in a third-world country.

You need to be more careful about this kind of language.
One of Amjad Daoud signatures I found on the web is:

Amjad Daoud
The OMLET Team Lead
Tera Knowledge Systems, Inc.
Arlington, Texas <--------------- This is not Jordan

Besides: What do you have against "guys from third-world countries" ?


-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

[Jeff Lanfield]

As the original poster I would agree here. If it is solicited and
relevant to the question asked it's not spam. I actually did end up
buying Mr. Celko's book and can attest that it is quite useful for
solving the class of problems I was interested in.

While in general people should not post ads for books they wrote on
tech newsgroups an exception should definitely be made for cases like
this one where the book contains pretty much the exact answer.

- Jeff
"Marshall Spight" <ms*****@dnai.c om> wrote in message news:<8KNFc.206 03$%_6.12831@at tbi_s01>...

"Daniel Morgan" <da******@x.was hington.edu> wrote in message news:1088887202 .344807@yasure. ..

I don't show favoritism when it come to calling spam spam. Even when it
is someone as esteemed as Joe Celko.

Spam is by definition unsolicited. An on-topic, helpful response to a
usenet post requesting help is by definition solicited. Your "integrity"
is just ball-busting on a guy who bends over backwards to help all
comers for free, and also happens to sell some books for which
he's probably lucky to clear $25,000 annually. (Trees and
Hierarchies in SQL doesn't have the same cachet as the latest
work by David Sedaris. In fact, it's Amazon's 16,754's most
popular item, right behind the $350 "Suunto Yachtsman Wristop
Computer Watch w/ Barometer and Compass", and you can
bet a $350 wrist barometer isn't exactly flying off the shelves.)

You're not engaging in spam-vigilance; you're just engaging
in gratuitous anticommerciali sm.

But exactly what is it you have contributed to c.d.o.server?

I could just as well ask what you've contributed to c.d.theory,
but that wasn't the point. The point was that this thread isn't
specific to your newsgroup; it spans multiple newsgroups.
Expecting someone on this thread to conform to the customs
of one of those newsgroups is as unrealistic as those folks in
Snakewater, Iowa, who get upset when they see something
that violates a local ordinance posted on the internet from
New York City.

But the absolute frosting on the cake, the hypocritical parsley
on the potatoes, is the fact that both of your posts on this
topic so far have included links to courses you teach at
Washington. You're giving those courses away for free,
I presume? Oh, no, wait, I see; Oracle Application Development
is $1875. At least Joe's links to his book on Amazon were
pertinent to the OP's question.
Marshall