SQL child with multiple 1-to-1 parents

Question

I have 3 tables:

Plane (ID, Name)        //Parent
Car(ID, Name)           //Parent
Engine(ID, Name)        //Child

Engine table must have a parent which is either Plane or Car. These tables are related with 1-to-1 relationship.

The main thing is that there is a need to get a parent through engine. How to correctly setup a database here?

---

I see multiple scenarios here but cannot figure out which is the best one:

1. Put engine ID at Plane and Car. The problem I am facing here is that before I query I need to know which parent engine belongs to.
2. Have plane_engine and car_engine tables. Same problem as above + more overhead with separate tables which are more required for many-to-many relationship (which is not a case here)
3. Have a soft key and type at engine table (Parent_ID, Type). Problem which is at 1 and 2 scenarios is reduced but I cannot ensure that Type and Parent_ID will be true and represent what it should (f.e. Type is Car but Parent ID is of plane).

Any insights would be great. Thank you!

Answer 1

1. Put engine ID at Plane and Car. The problem I am facing here is that before I query I need to know which parent engine belongs to.

This models N:1 between Car and Engine (and between Plane and Engine).

You'll need to make the Car.EngineID UNIQUE to have 0..1:1 (0 because this still allows a car-less engine). Otherwise, this looks fine for your stated requirement, and the index on Car.EngineID should ensure good performance when getting car(s) that correspond to a known engine.

2. Have plane_engine and car_engine tables. Same problem as above + more overhead with separate tables which are more required for many-to-many relationship (which is not a case here)

This models N:N as you rightfully noted.

It can be restricted to 0..1:N or N:0..1 or even 0..1:0..1 by making appropriate fields UNIQUE, but is generally unnecessary if you don't actually need N:N.

3. Have a soft key and type at engine table (Parent_ID, Type). Problem which is at 1 and 2 scenarios is reduced but I cannot ensure that Type and Parent_ID will be true and represent what it should (f.e. Type is Car but Parent ID is of plane).

This actually models 1:N between Car and Engine (and Plane and Engine). And as you noted it doesn't allow proper enforcement of foreign keys.

If 1:N is what you actually need, you can simply put...

PlaneID REFERENCES Plane
CarID REFERENCES Car

...in the Engine. And you can enforce the exclusivity (so the same engine cannot be user for car and plane)...

CHECK (
    (PlaneID IS NOT NULL AND CarID IS NULL)
    OR (PlaneID IS NULL AND CarID IS NOT NULL)
)

On the other hand, if you need 1:0..1 between Car and Engine, then just put a UNIQUE constraint on Engine.CarID. Ditto for planes.

Or you can use inheritance.