CODEWITHSUNDEEP
sqldatabase-designshared
Jonathan Ingram
Jonathan Ingram

Reputation: 1

shared relationships in database

Sorry to be so ignorant.... but I am trying to set up a database of five tables.... Truck_fleets, cabs, trailers, tyres, tyre_pressures. Should be straight forward except "tyres" needs to be a child of both "cab" and "trailer". both the cab of a truck and the trailer of the truck have tyres!

In setting it up I end up with two keys in "tyres" only one of which is satisfied. that is the tyre belongs to either the cab or the the trailer.

Any suggestions or a reference where I can read about this please.

thanks

Upvotes: 0

Views: 93

Answers (2)

emperorz
emperorz

Reputation: 427

Try two relation tables. One connects cabs to tyres, call it cab_tyres and one connects trailers to tyres, call it trailer_tyres.

Each table holds two id's. 

cab_tyres: 
 int cab_id   //FK to cab table
 int tyre_id  //FK to tyre table

trailer_tyres: 
 int trailer_id //FK to trailer table
 int tyre_id    //FK to tyre table

To elaborate a little, the theory here is that entity relations are themselves entities, and are typed according to the things they relate. You don't mix types normally, thus we have two relation tables.

If you wish to relate the cab and it's trailer, then that would be another relation entity and so on.

Upvotes: 1

GarethD
GarethD

Reputation: 69809

This really depends on how much cabs and trailers have in common. One approach would be the route you have gone down having two fields in your tyre table, you would probably also want a check constraint to ensure that only one is null, and that both are not null to ensure that any tyre has one, and only one parent.

Another option would be to store trailers and trucks in a single table, and have a Type field that stores whether it is a truck, or a trailer, then your tyre table only needs a single field to link to the ID of this main table. You will just need to ensure that this table has all the columns that you had in truck and trailer.

Another option would be to use inheritance, this would be similar to the single table approach, but if there are not very many overlapping columns between the two types, or non shared children this can be a better approach. So you might have tables like so:

Vehicle { VehicleID (PK), SharedColumn1, SharedColumn2 }

Truck { VehicleID (PK, FK [Vehicle]), TruckColumn1, TruckColumn2 }

Trailer { VehicleID (PK, FK [Vehicle]), TrailerColumn1, TrailerColumn2 }

Tyre { TyreID (PK), VehicleID (FK [Vehicle]), TryeColumn1 }

TruckOnlyChild {ID (PK), VehicleID (FK [Truck]) }

Each of the 3 approaches has it's pros and cons, you really need to chose which is preferential to you. For what it is worth, I personally would go for one of the second two options, depending on my requirements. If the attributes were sparse enough I would consider the 2nd option but using EAV instead of having lots of null columns

Upvotes: 0

Related Questions