CODEWITHSUNDEEP
ruby-on-railsactiverecordassociationsmodel-associations
Grant Sayer
Grant Sayer

Reputation: 2330

ActiveRecord Association - Design Question

Overview

I have a set of data models for a Sailing club example, where there are Members of the club, who have a relationship with a Boat (eg Owner, Skipper, Crew). Currently wrestling with the best way to design the ActiveRecord associations to answer some questions on getting data. This would include such queries as:

First Design Attempt

So my first design was to have a Boat <- many to many -> Member structure using the has_many_through association type. The join table would carry the attribute for the type of relationship with the Boat This looks like:

class Boat < ApplicationRecord
    has_many :boat_crews
    has_many :members, through: :boat_crews
end

class Member < ApplicationRecord
    has_many :boat_crews
    has_many :boats, through: :boat_crews
end

class BoatCrew < ApplicationRecord
    belongs_to :member
    belongs_to :boat
end

The table BoatCrews has the usual attributes id, member_id, boat_id and also includes another attribute called role:string to represent the relationship to the boat. So when a Member is associated to a boat and entry is placed in the join table to hold the join information with the role. If a Member is both and owner and say a skipper of the boat then there are two roles in the BoatCrews table.

Now what this enables is the expected ActiveRecord Association queries such as

b = Boat.find(1)
b.members # show all the members associated with the first Boat

The only downside is that if a Member has two roles (Owner, Skipper) then the b.members query will return an array which contains one duplicate (ie. the Member with two roles). This can be removed with a b.members.uniq

Is there a better design where I model the role/relationship to the Boat differently, hence Design attempt number 2

Second Design Attempt

Thinking on it some more I thought that I could seperate the idea of the member and the relationship to the concept of a boat and a generic crew. So new model attempt is

class Member < ApplicationRecord
    has_one :crew
end

class Crew < ApplicationRecord
    has_many :boat_crews
    has_many :boats, through: :boat_crews

    belongs_to :member, optional: true
end

class Boat < ApplicationRecord
    has_many :boat_crews
    has_many :crews, through: :boat_crews
end

class BoatCrew < ApplicationRecord
  belongs_to :boat
  belongs_to :crew
end

The idea is that a Boat has a Crew collection and vice versa with the Crew model identifying the type of Crew role (eg crew, skipper, owner). The association of the Member to Crew models is to then take the Member information (eg name, etc) and associate to the role they have with the Boat crew model.

The flaw in this design is that the has-one relationship defined between the Member and Crew models means that foreign_key faults occur if you try to do a member#crew.build type creation.

Is there a better option

At this point neither approach seems correct. The first design works, but requires use of an additional constraint (needing to use uniq method). The second design doesn't work and on reflection is probably correct in that the semantics I've defined is 'has-one' but I'm really got more than one (eg Member has two roles as part of Boat).

Some ideas I've thought about are

So seeking any suggestions or comments on how to tackle this one ?

Upvotes: 0

Views: 58

Answers (1)

Ben Garcia
Ben Garcia

Reputation: 386

Unless I'm misunderstanding something, I don't think your design attempt 2 solves for the 'problem' that arises with design attempt 1. If a member can only have one 'crew', then they would still be doubly counted when being considered a part of a boatcrew, now you have a duplicate crew (that points to the same member).

Though this isn't a perfect rule, when doing initial architecture, it's a decent rule-of-thumb to model things as close to the real world as you can. Optimizations can come later. In this case, I would go with attempt 1, but don't use uniq (because that would require loading everything into ruby, and then getting distinct record, which is slow). Rely on rails Active Record Query Interface instead. You can use something like,

b.members.distinct

which generates a SELECT DISTINCT ... query for when you need unique members. Realistically though, it's highly likely you want to query for knowledge of the members and their roles. I'm not sure of the easiest way to do this in pure rails active query interface, but, you can always use array_agg if you want to get the member and all of their roles.

For example

SELECT members.name, array_agg(boat_crews.role) AS roles FROM members
INNER JOIN boat_crews on members.id = boat_crews.member_id
INNER JOIN boats on boat_crews.boat_id = boats.id
WHERE boats.id = 1
GROUP BY members.name

would return something like


names | roles
joe   | ["rower"]
john  | ["skipper","captain"]

Will get you the names of all members, and all of their respective roles as an array. I leave it to you to convert this to an active record query.

A rather long-winded way of saying go with option 1, and ask specific questions here when you need help with certain queries.

Upvotes: 1

Related Questions