Relationships - how would you do this?

Hi there,
I have a database design problem and I don't know how to go about this. Any
help would be greatly welcome!
I've simplified my tables for the sake of understanding. I've created a
fictional scenario that is exactly the same as my more complex one. My
example involves the selling of three unique items - cars, boats and planes.
Each item has it's own table that is distincly different than the other two
items (eg - car fields are totally different than boat or plane fields). My
tables are as follows:
tbl_Inventory - Simply contains a list of Cars, Boats and Planes to be sold.
tbl_Cars - A detailed list of all of the cars for sale
tbl_Boats - A detailed list of all of the boats for sale
tbl_Planes - A detailed list of all the planes for sale
Now, how would you go about linking these tables together. The way I did it
was by using the following two fields in my tbl_Inventory table:
Ref_ID - The ID of the table we are referencing
Ref_Type - The actual type of table we are referencing (1 is car, 2 is boat,
3 is plane)
Eg - Ref_ID = 6, Ref_Type = 2 would mean that in the boat table we are
looking at boat ID 6.
This seems okay but how do you create a relationship for this type of layout
that would, for example, allow for cascade deleting of a record in the
tbl_Cars table to delete the associated car record in the tbl_Inventory
table?
I've tried adding a primary key to tbl_Inventory to both the Ref_ID and
Ref_Type fields but then how do you generate a relationship? Am I doing
something wrong?
Any help would be appreciated.
Chris.(Irritating Access-style tbl_ prefixes ignored. We already know these are
tables!)
CREATE TABLE Inventory (ref INTEGER PRIMARY KEY, type CHAR(1) NOT NULL CHECK
(type IN ('C','B','P')) /* Car/Boat/Plane */, UNIQUE (type,ref), /* Common
columns such as description, price, quantity, etc in this table */)
CREATE TABLE Cars (ref INTEGER PRIMARY KEY, type CHAR(1) NOT NULL DEFAULT
'C', CHECK (type='C'), FOREIGN KEY (type,ref) REFERENCES Inventory
(type,ref) ON DELETE CASCADE)
CREATE TABLE Boats (ref INTEGER PRIMARY KEY, type CHAR(1) NOT NULL DEFAULT
'B', CHECK (type='B'), FOREIGN KEY (type,ref) REFERENCES Inventory
(type,ref) ON DELETE CASCADE)
CREATE TABLE Planes (ref INTEGER PRIMARY KEY, type CHAR(1) NOT NULL DEFAULT
'P', CHECK (type='P'), FOREIGN KEY (type,ref) REFERENCES Inventory
(type,ref) ON DELETE CASCADE)
David Portas
SQL Server MVP
--|||This is great. Thanks a tone David.
Chris.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:cIqdnSHZq-FJlWncRVn-2w@.giganews.com...
> (Irritating Access-style tbl_ prefixes ignored. We already know these are
> tables!)
> CREATE TABLE Inventory (ref INTEGER PRIMARY KEY, type CHAR(1) NOT NULL
> CHECK (type IN ('C','B','P')) /* Car/Boat/Plane */, UNIQUE (type,ref), /*
> Common columns such as description, price, quantity, etc in this table */)
> CREATE TABLE Cars (ref INTEGER PRIMARY KEY, type CHAR(1) NOT NULL DEFAULT
> 'C', CHECK (type='C'), FOREIGN KEY (type,ref) REFERENCES Inventory
> (type,ref) ON DELETE CASCADE)
> CREATE TABLE Boats (ref INTEGER PRIMARY KEY, type CHAR(1) NOT NULL DEFAULT
> 'B', CHECK (type='B'), FOREIGN KEY (type,ref) REFERENCES Inventory
> (type,ref) ON DELETE CASCADE)
> CREATE TABLE Planes (ref INTEGER PRIMARY KEY, type CHAR(1) NOT NULL
> DEFAULT 'P', CHECK (type='P'), FOREIGN KEY (type,ref) REFERENCES Inventory
> (type,ref) ON DELETE CASCADE)
> --
> David Portas
> SQL Server MVP
> --
>|||I would not do it that way, I would setup a properties table that held the
unique attributes between the boats, planes and cars. This would allow you
to use the same code for all types of products. If you start selling
motorcycles you will have to rewrite your code.
Jim
"ChrisN" <cnewald@.hotmail.com> wrote in message
news:#f3dHgYAFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Hi there,
> I have a database design problem and I don't know how to go about this.
Any
> help would be greatly welcome!
> I've simplified my tables for the sake of understanding. I've created a
> fictional scenario that is exactly the same as my more complex one. My
> example involves the selling of three unique items - cars, boats and
planes.
> Each item has it's own table that is distincly different than the other
two
> items (eg - car fields are totally different than boat or plane fields).
My
> tables are as follows:
> tbl_Inventory - Simply contains a list of Cars, Boats and Planes to be
sold.
> tbl_Cars - A detailed list of all of the cars for sale
> tbl_Boats - A detailed list of all of the boats for sale
> tbl_Planes - A detailed list of all the planes for sale
> Now, how would you go about linking these tables together. The way I did
it
> was by using the following two fields in my tbl_Inventory table:
> Ref_ID - The ID of the table we are referencing
> Ref_Type - The actual type of table we are referencing (1 is car, 2 is
boat,
> 3 is plane)
> Eg - Ref_ID = 6, Ref_Type = 2 would mean that in the boat table we are
> looking at boat ID 6.
> This seems okay but how do you create a relationship for this type of
layout
> that would, for example, allow for cascade deleting of a record in the
> tbl_Cars table to delete the associated car record in the tbl_Inventory
> table?
> I've tried adding a primary key to tbl_Inventory to both the Ref_ID and
> Ref_Type fields but then how do you generate a relationship? Am I doing
> something wrong?
> Any help would be appreciated.
> Chris.
>sql