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.
VehicleandVehicleTypetables then. Your Party-Person-Organization example applies well to my scenario. Thanks for helping me think that through! – Jake Reece Aug 08 '19 at 12:54