Modeling Multiple Many-to-Many Relationships With One Model In Common

[Neil Chaudhuri]

I have a fairly straightforward modeling task involving People,
Albums, and Photos. Here are the business rules:

*One person can have many photos.
*One album can have many photos.
*One album can have photos of many people.
*An album can have multiple photos belonging to the same person, but
not necessarily all the photos belonging to that person.
*Given an album, I would like some way to aggregate sets of photos by
the person they belong to (where each set is a subset of all the
photos that person has). So for example, I would like to say that in
Album 10, Bobby has his photos 9, 17, and 41 and Sally has her photos
47, 57, 93, 201, 203. Of course, Bobby and Sally both have a lot more
photos than that but just not in this album.

I would think the tables would look like this:

Person
id | name

Album
id | name

Photo
id | filename

PersonAlbumPhoto
person_id | album_id | photo_id

My questions are these:

*Is this a reasonable model for the business rules as I described? I
am happy to hear of something more elegant or efficient.
*How should I model my Ruby classes for Person, Album, and Photo to
generate what I need?

Thanks for the insight.

[Neil Chaudhuri]

For those who might find this useful, I altered my calculus slightly
and went this route:

Person
has n, :photos

Album
has n, :photos, :through => Resource

Photo
belongs_to :person, :required => true
has n, :albums, :through => Resource

Now if I could just figure out how to query on a many-to-many.

Thanks.

[mltsy]

>
> Now if I could just figure out how to query on a many-to-many.
>

Querying shouldn't be too hard - have you figured it out?

You can use:
@photo.albums
@album.photos(:person => p)
@person.photos(:album => a)
Photos.all(:album => a, :person => p) (to get all photos from person p
in album a)
or even
Albums.all('photos.person' => p) (to get all albums containing at
least 1 photo from person p. This only works using the string syntax)

-Joe

[Neil Chaudhuri]

I was able to figure that out eventually. Thanks for listing all the
options!