[dba-SQLServer]Referential integrity question (Generalization/Specification)

I have done something like this, and would approach your problem like this:

tblVehicles
	vehicleID - autonumber
	group - contains either "C" or "T"
	... Common columns

tblCars
	vehicleID - integer copied from tblVehicles
	group - always contains "C"
	... Unique car columns

tblTrucks
	vehicleID - integer copied from tblVehicles
	group - always contains "T" 	
	... Unique truck columns

The PK on the tblVehicles table comprises the first two columns. The PK on
tblCars and tblTrucks is the same. Then create a pair of queries that join
on the PK to tblCars and tblTrucks. In other words, no trucks will appear in
one query and no cars in the other.

For ease of implementation, I would present the list as read-only, and then
when a user double-clicks an item in the list, I know immediately which form
to open (frmCarDetails or frmTruckDetails). On the master form
(frmVehicles), when the user wants to add a record, as soon as she chooses
the group I know which table to add a row to.

In my situation, I needed about 10 different detail types, so I put them in
a table containing a column for the form name, which eliminated pretty much
all the code required to deal with this. I just looked in the table, grabbed
the form name and opened it.

Hth,
Arthur

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Michael
Brosdorf
Sent: July 16, 2003 3:53 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer]Referential integrity question
(Generalization/Specification)


Dear group,

for an application I need to implement a generalization of data objects. As
an example, let there be 3 tables:

1. Vehicles
2. Cars
3. Trucks

Every vehicle is, of course a vehicle with some common properties (for
instance steering wheel diameter :-)). But then it is either a car or (xor)
a truck, which both have different properties that apply only to the
respective type of vehicle (e.g. number of passenger seats for cars etc.).

Every vehicle has an entry in tblVehicles with a unique Vehicle_ID.
Additionally, every vehicle has an entry in either tblCars (Cars_ID is PK,
Vehicle_ID is FK) OR tblTrucks (with Truck_ID as PK and Vehicle_ID as FK).

A given Vehicle_ID can be in only one of the specific table.

Is there any way to enforce referential integrity in a scheme like that
using SQL Server 2000 Constraints or other mechanisms? If not, has anyone
implemented something like that (using triggers?) and would like to share
his/her experiences?


TIA,


Michael