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.

--

.

Relevant Pages

  • Re: AutoNumber Question
    ... particular field only applies if that field is a Primary Key? ... SSN or VIN anaolgies, couldnt one still ensure the uniqueness of them, even ... database, as it would be if it was part of relationships. ... important one) of a vehicle. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: how do i set up a vehicle maintenance database?
    ... ModID (Autonumber, Primary Key) ... Other stuff that only applies to that particular vehicle ... Other tables may also be needed - the car people here and you yourself can ...
    (microsoft.public.access.gettingstarted)
  • Re: Selecting data that matches all conditions
    ... than one owner, you would have schema more like this: ... NOT NULL PRIMARY KEY, ... vin CHARNOT NULL ... That is I want the name BMW when I ask for the car owned by ...
    (microsoft.public.sqlserver.programming)
  • Re: AutoNumber Question
    ... particular field only applies if that field is a Primary Key? ... database, as it would be if it was part of relationships. ... A VIN as a PK could get a bit murky for an insurance company or the DMV ... If it were a Database containing vehicle ...
    (microsoft.public.access.tablesdbdesign)
  • Re: AutoNumber Question
    ... particular field only applies if that field is a Primary Key? ... database, as it would be if it was part of relationships. ... A VIN as a PK could get a bit murky for an insurance company or the DMV ... If it were a Database containing vehicle ...
    (microsoft.public.access.tablesdbdesign)
Loading