Topic: Modelling pairs with named roles (e.g. Husband-Wife)

I was wondering if anyone here had experience, in Rails, of modeling pairs with named roles, such as "Husband" and "Wife" or "Leader" and "Follower" (the latter pair is what I'm working with in my dance application).

As far as I know there are two main design schools for this.

Named Column
So the table design would be
id, leader_id, follower_id, [+ anything else needed]
in this both leader/follower_id are foreign key links back to the People table.

Role Column
This method would typically require two tables
id, [any extra, possibly none]

id, couple_id, person_id, rolename, [any extra]

Where rolename would be either 'Leader' or 'Follower', etc (Or if you wanted, links to a look-up table/dynamic domain.

In the previous version of the application (PHP) I used the "Named Columns" approach.  I'm re-impementing the application, not just porting it, so I'm not constrained by that.   The first version makes queries of the form, get a list of all people registered slightly more cumbersome (ie either SELECT leader_id.... UNION follower_id or SELECT ... WHERE (peoplied=leader_id or peopleid=follower_id) etc)  While the second form will require extra joins to re-assemble a particular couple.  I've just about convinced myself that the performance implications are just about balanced -- when taking into account which queries are most used by back-end cron jobs and which are used by regular/admin users.

So the question really comes down to, is one of the two formulations more "Railish" or is there no real difference to rails?  Is there another formulation I should be looking at?  I was thinking that the second formulation, could be especially nice, if extra associations with conditions are added to simulate the first form at the application level, while still making it easy to express some of the "report" style queries.


My RoR journey  -- thoughts on learning RoR and lessons learned in applying TDD and agile practices.

Re: Modelling pairs with named roles (e.g. Husband-Wife)

Interesting topic. Right off the bat the Role Column approach feels more Railsish because it makes use of the has_many :through association (person has many Couples through Couple Memberships), but I think the correct approach depends largely on the domain.

I am facing a similar situation on an ordering system I am working on. Each order has a billing address and a shipping address. I am leaning towards the Named Column approach because I do not need that Address has many Orders relationship - there is a third model, Account, which handles that. Account has many addresses and many orders.

I think it depends a lot on how you will be accessing the relationship. The Named Column approach makes it more difficult to fetch all the couples for a given person, but easier to fetch the Leader or Follower of a given couple. The Role Column approach is the opposite: easier to fetch the couples for a given person, but harder to fetch the follower and leader for a given couple. The Role Column approach also feels a bit more CRUDy, but again I think that depends on the rest of the domain and how you plan to handle that relationship.

Railscasts - Free Ruby on Rails Screencasts