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.
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.
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.