Re: modeling either/or relationship...

Murdoc wrote:

> -CELKO- wrote:
>

>> 
>> Now start hiding all this stuff in VIEWs immediately and add an INSTEAD
>> OF trigger to those VIEWs.

This will work if and only if all there is to a car is a unique vin, a vehicle_type and a door count, and vin is a primary key for every car.

Both assumptions hold in this example, but both are usually violated in the wild.

What if you need to model a class of cars without vin (a variant of this shows up often in employee databases that take SSN as a primary key and suddenly have to deal with foreign employees)? What if there are duplicate vins in different contexts? Two license plate numbers from different countries for example may be identical for two different cars, maybe there is a similar thing with your vins. Maybe you need to model vehicles from a country where the laws use two numbers to identify chassis and engine, and one single vin is meaningless? Then your domain may still consider a vehicle an entity, but different vehicle_types may have different requirements for primary keys. That is a tricky yet frequent case in real-life databases.

The other assumption is weak, too. Your cars will have more than one attribute. The set of attributes for a SED and a SUV are almost guaranteed to differ. You may get away with one broad table that contains a superset of all possible attributes of all vehicle types, but then again, Murphy's Law says you won't.

> In you example, how would you structure the query to find a listing of all
> 4DR cars currently in your database (assuming that both Sedans and SUVs
> can be 2-door and 4-door vehicles)?

As Joe wrote, you wouldn't. You would hide all this complexity in VIEWs.

-- 
Ernst-Udo Wallenborn