Re: Eliminating Combinatorial Relationship Multiplication
Daniel Morgan <damorgan@x.was hington.edu> wrote in message news:<108883185 5.922891@yasure >...[color=blue]
> --CELKO-- wrote:
>[color=green]
> > 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.[/color]
>
> 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.[/color]
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.
Daniel Morgan <damorgan@x.was hington.edu> wrote in message news:<108883185 5.922891@yasure >...[color=blue]
> --CELKO-- wrote:
>[color=green]
> > 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.[/color]
>
> 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.[/color]
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.
Comment