Dec 22, 2007

Adventures in Self-Referential Database Associations

Rails makes object associations easy and has a number of different ways to configure associations and even store extra information about each relationship. Most of the time, those relationships are between business objects that wrap rows in two separate database tables. For example, let's say we wanted to be able to record, read and update grades and attendance of students in a particular school class. We might create a student model and a school class model, and the association between them may contain the grades and attendance information. We use many of these kinds of model object associations, and the procedure is fairly straightforward and written about extensively. See the following links to learn more:

For our project, we also needed a way to store various qualities about relationships between pairs of objects of the same model type, i.e. between rows of the same database table. We would need this for a number of different purposes. Those creating 'social net' applications know this kind of association well; they must document, for example, who are the direct friends of a particular user, and possibly further information about each such relationship. All those users are stored in the same database table, so a separate "join" table is created to store the keys of both users, plus any other information about the relationship.

To accomplish this, we used self-referential has_many :through associations that use a join model and methods to simplify the reading and updating of attributes of the association. To read more about these more specialized associations, here are some sites:

More details on this later.

No comments: