sql server new users: 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.

[quoted text, click to view]
-snip-

I suggest that you look at your design again. There is no real reason why
cars/planes/boats should have different tables, they may have different
fields, but why not use meta data to describe what those fields contain?