2

I have a new requirement that for a given User how many vehicles he can drives. At present this information needs to be shown as a list of vehicles. There can be different types of vehicles, for example:

User A

Cars


  1. SUV 1
  2. Toyota RED

Bikes


  1. Fav Yellow
  2. Rocker blue

And so on...

The users are normally family members hence they share the vehicles and therefore the association is many to many for user and vehicle and there can be many more types of vehicles in future so, for each vehicle I need to change my User Entity and add an association for each vehicle type which seems bad.

Sample snippet:

class UserEntity {
  @ManyToMany()
  @JoinTable(...)
  Set<Car> cars = new HashSet<>();

  @ManyToMany()
  @JoinTable(...)
  Set<Bike> bikes = new HashSet<>();

  ...same for each type
}

Right now I have created my own association table user_vehicle which contains following three fields:

ID | user_id | vehicle_id | vehicle_type

The application is built on Spring and I am using Spring data JPA but I cannot directly define many to many association from user as it contains extra column type in my association table hence I have to manually manage changes. For example for creating a new record I am persisting main entity first and then persisting this child entity with the ID of the parent entity and same for the update. This seems a coupling to me as any new developer needs to understand the correct order of persisting entities.

I have a feeling that there is some issue in the above approach, would like to hear from others how they would handle this requirement?

  • 3
    So you already know you conceptionally need an association (or "link") table, and you want to know if "Spring Data JPA" can handle this automatically for you? Then this is not a good question for this site, tool related questions are much better suited for Stackoverflow. – Doc Brown Nov 8 '17 at 13:32
  • @DocBrown not exactly! what can be a better approach at domain level? – CodeYogi Nov 8 '17 at 14:31
  • The problem with your approach is including vehicle type in the association table. You've denormalized your data. Grouping by type is something to do for computation but you can do so without having vehicle_type in the association. – Aluan Haddad Nov 13 '17 at 15:50
1

Many to many relationships are usually a sign that entities require further decomposition. In the case you describe, what you really have is a one to many. A person can drive more than one vehicle.

So the relationship is Person->PersonVehicle->Vehicle

PersonVehicle would just contain the list of person and vehicle keys. The primary key would typically be a compound key consisting of the two keys.

EDIT: Worked example

I've supplied an example of what I've described below. In essence, this decomposes the many-to-many into two one-to-manys.

Person (key, description)

1, Lance

2, Bill

PersonVehicle (person key, vehicle key)

1,1

1,2

2,1

2,2

Vehicle (key, description)

1, Humvee

2, Jeep

  • 3
    A vehicle can be driven by more than one person. The relationship is many-to-many. Though I do agree that often times many-to-many relationships are usually a sign that entities require further decomposition. – Neil Nov 8 '17 at 11:54
  • But what if two vehicle keys are equal? can you please elaborate your answer? – CodeYogi Nov 8 '17 at 12:02
  • @Neil In a logical design, they do of course exist. In a physical DB they are almost always replaced with a link table. A good way to think of it is point in time. A person can only drive one vehicle at a time. – Robbie Dee Nov 8 '17 at 12:13
  • @CodeYogi I've added a bit more detail. – Robbie Dee Nov 8 '17 at 12:30
  • @RobbieDee Ah in that sense. I agree, but I don't know if this is what the OP intended. At that point wouldn't it make more sense to call it Driver rather than PersonVehicle? – Neil Nov 8 '17 at 13:27

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.