0

I need the ability to associate two entities of the same or different types. Take the concept of planes and trucks that have the ability to radio to each other.

+----------------------+
|        Airplane      |
+----------------------+
| AirplaneId           |
| Wingspan             |
| MaxTakeoffWeight     |
| etc...               |
+----------------------+

+----------------------+
|         Truck        |
+----------------------+
| TruckId              |
| CargoCubicFt         |
| IsResidential        |
| etc...               |
+----------------------+

+----------------------+
| CommunicationBetween |
+----------------------+
| [AirplaneOrTruckId]  |
| [AirplaneOrTruckId]  |
+----------------------+

What's the best way to accomplish these associations?

Notes

I can't combine or abstract out the two entities (e.g. can't combine planes and trucks into a Vehicles table) because each entity has many unique properties that the other doesn't have.

The only other idea I have is to create a table like this:

+----------------------+
| CommunicationBetween |
+----------------------+
| Entity1              | -- "Airplane" or "Truck"
| Id1                  | -- AirplaneId or TruckId
| Entity2              | -- "Airplane" or "Truck"
| Id2                  | -- AirplaneId or TruckId
+----------------------+

... but that's not an elegant solution.

  • 3
    Among other related points, the excerpt "I can't combine or abstract out the two entities (e.g. can't combine planes and trucks into a Vehicles table) because each entity has many unique properties that the other doesn't have." suggests that representing the scenario with the aid of a conceptual supertype-subtype relationship would be very advantageous. Establishing some analogies, you may find of help Q & A no. 1 and Q & A no. 2 in that regard. – MDCCL Aug 8 '19 at 2:46
  • @MDCCL Hmm, maybe it would be best to establish Vehicle and VehicleType tables then. Your Party-Person-Organization example applies well to my scenario. Thanks for helping me think that through! – Jake Reece Aug 8 '19 at 12:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.