I have three tables with a relationship I've never worked with
before. Can anyone suggest/comment on the best way to create a third
normal form relationship between these tables?

The tables basically are:

TRAIN (TRAIN_ID and 15 columns about train specs, etc)
TRUCK (TRUCK_ID and 12 columns about truck specs, etc)
TRANSPORTATION_ITEM
This table has, among others, two columns, TRUCK_ID and TRAIN_ID. If
the truck column is used there can be no data in the train column and
vice versa.

This relationship seems denormalized to me, but I don't remember how
to normalize it. Does anyone know the correct name for this kind of
relationship?

thanks!

Assuming you aren't repeating data in TRANSPORTATION_ITEM then you are
already satisfying one of the normalisation principles.i.e minimising
redundancy.
Even though what you are doing in TRANSPORTATION_ITEM will work, I would
separate into 2 tables with distinctive names for the tables.


--

Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://www.itjobfeed.com">UK IT Jobs</a>


<bbcrock (AT) gmail (DOT) com> wrote

(bbcrock (AT) gmail (DOT) com) writes:
It's difficult to say exactly from this scant information. Judging from
the anme, TRANSPORTATION_ITEM is a supercalls, of which TRAIN and TRUCH
are heirs, to speak in object-orirent terms. In such case, it feels
backwards to me that TRUCK_ID and TRAIN_ID appears in TRANSPORTATION_ITEM.
I'd rather have a TRANSPORTATION_ITEM_ID that also would be the key in
thr TRAIN and TRUCK tables.

But the purpose of TRANSPORTATION_ITEM may be different, in which case
TRAIN_ID and TRUCK_ID as mutually exclusive columns makes perfect sense.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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.

I think there are a couple of ways you can go here. First is an
assessment of what the business logic behind the TRANSPORTATION_ITEM
table is. If it simply for output for the interface then move the
business logic up into code. If that is not possible, or there is
truly data that needs to be saved for another purpose, the the other
route would be to add the TRANSPORTATION_ITEM id to the truck and
train table. That should give you the normalization you require and
reduce the NULL fields in the TRANSPORTATION_ITEM table which is what
it appears you are really after. Then your query can join the from
the train or truck table to the TRANSPORTATION_ITEM table.

On Mar 4, 11:00 am, bbcr... (AT) gmail (DOT) com wrote:
You can have two extra columns. For transportation_item table one
column will be source with values 'truck' or 'train'. The other column
is id with values for truck_id or train_id.

On Mar 5, 11:02 am, "myke.dil... (AT) gmail (DOT) com" <myke.dil... (AT) gmail (DOT) com>
wrote:
Yeah, I was really unclear when I wrote that initial post- I had to
get it out and move onto something more pressing. There is a one to
many relationship where truck and train "types" are used in multiple
transportation_item records. So that would increase the amount of
truck records if I included transportation_item in that table.

thanks!

This is the time when I mention that I purchased Joe Celko's "SQL For
Smarties" several years ago and felt it was a great companion for
learning standard SQL theory. I recommend the book for everyone who
wants to take it to the next level.

I suspect that this entire database I inherited from a group project
of multiple contractors will have to be overhauled to achieve true
third normal form, but cannot be for financial and scope reasons.

I certainly was able to identify 3 mutual columns between the trucks
and trains and can suggest building a join table with that data and a
vehicle_ID that's used as the join with the transportation_item.

thanks!

Why, so do I!! An amzaming cooncidence !!