Re: modeling either/or relationship...

Ernst-Udo Wallenborn wrote:

> Murdoc wrote:
>
> > -CELKO- wrote:
> >
> >>
> >> Now start hiding all this stuff in VIEWs immediately and add an INSTEAD
> >> OF trigger to those VIEWs.
> >
> > All this seems slightly over-complicated for what appears to be a simple
> > issue. Why create 7 tables, when 1 will do?
> >
> > Vehicle -> [#vin, vehicle_type, door_count]
>
>
> 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.

Given that the solution illustrated had only those fields, then I only included those in my suggestion. Given that every vehicle has both a vehicle type and a door count, the only assumption that needs work in the VIN being a primary key.

> 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)?

I am unfamiliar with SSNs.

> 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.

Solution: Make the license_plate & country combination unique.

>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 use those two numbers? Or create an internal unique index as the primary key, and remove the database-level uniqueness constraint. Use Triggers and Stored Procedures to enforce integrity.

> 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.

Maybe. Another potential solution could be to define multiple unique indexes on the same table. Can you provide an example of a real-life situation, though? I would be intrigued.

> 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.

Maybe. But my question would be: What attributes are valid only for a sedan and not an SUV? Or, more specifically, what attributes are valid for a 4DR SUV as opposed to 2DR SUV? Or Sedan?

> 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.

My question still stands. In order to list all 4 door vehicles in the system (using the hierarchical design suggested), the query would have to visit EVERY specific car type in the system.

Essentially, the end result for the proposed 'hierarchical' design is a nightmare:

Vehicle

	SUV
		4-Door
		2-Door
	SED	
		4-Door
		2-Door
	STW (Station Wagon)
	HTC (Hatchback)
	TRK (Truck)
	SMI (Semi-Trailer)
	RDT (Road Train)
	UTE

The list would be endless.

--