Topic: Self referential relationship vs. separate model design question

I'm planing an application that is supposed to manage DVD collections.

To simplify things a bit let's presume that users have collections, collections contain dvds, and dvds contain either movies or tv series episodes.

However a collection could also contain some box sets, which in turn contain several dvds. (Here I'm talking about box sets that contain complete dvds that include their own cases and in some cases can also be bought separately as a standalone product. e.g. James Bond box...)

Now technically and conceptually a box set is a separate concept/entity then dvds. However from the practical standpoint they can be almost always be treated the same.
For instance when searching for a users collection items we would want to treat them as the same and display them together (interleaved based on whatever sorting criteria).

Or to go a bit further and do something more advanced, the user might choose between two views:
1. listing all standalone dvds plus the boxes, but not the dvds inside the boxes
2. listing all dvds, including those from sets, but not the boxes themselves.

Looking at the problem from the technical standpoint and with a "how would I do it in PHP" view I came up with the following (possibly not to bright) solution:

- do not distinguish dvds and boxes
- dvds now can contain movies etc. or other dvds
- boxes are entered as dvds that happen to contain other dvds
- the dvd table would have a parent_id field
- if a dvd belongs to a box the parent_id field is populated with the id of the box
- if a dvd is a standalone then the parent_id is left blank
- View 1 would be achieved by the SQL only returning dvds with null parent_ids
- View 2 is bit trickier.One would need to left join the same table to merge in the children and then filter those lines out that actually have children...

In rails the same could be achieved through a self referential relationship I guess...

However my problem is that while this might work I'm not convinced that it is actually the right design. What do you think?

- Should a dvd and a box be to separate models?
- Would that mean two separate tables?
- Or would that not make it inefficient and cumbersome to list them together?

Any ideas are greatly appreciated!

Balazs

Last edited by bjavor (2007-01-10 17:34:55)

Re: Self referential relationship vs. separate model design question

bjavor wrote:

- do not distinguish dvds and boxes
- dvds now can contain movies etc. or other dvds
- boxes are entered as dvds that happen to contain other dvds
- the dvd table would have a parent_id field
- if a dvd belongs to a box the parent_id field is populated with the id of the box
- if a dvd is a standalone then the parent_id is left blank
- View 1 would be achieved by the SQL only returning dvds with null parent_ids
- View 2 is bit trickier.One would need to left join the same table to merge in the children and then filter those lines out that actually have children...

This is an excellent solution, I was going to suggest it before I finished reading your post. wink

Rails actually has something to make this easier: acts_as_tree. To do the two views, you can just use the "parent_id IS NULL" for the first condition, and "parent_id IS NOT NULL" for the second condition. It would get trickier if the tree goes deeper than one level (to only retrieve the dvds at the bottom of the tree), but it doesn't sound like this will be the case.

Edit: Oh, nevermind, it would get a little more complicated than a simple is/is not null condition. What was I thinking? I'll try to think of something and post again. smile

Last edited by ryanb (2007-01-10 17:53:56)

Railscasts - Free Ruby on Rails Screencasts

Re: Self referential relationship vs. separate model design question

You'd just need one table and you could do it via STI (Single-Table Inheritance.  It's where you add a 'type' column to the table).

If you had this:

class DvdItem < ActiveRecord::Base # this means the table will be named 'dvd_items'
end

class Dvd < DvdItem
end

class BoxSet < DvdItem
end

# find all dvds and box sets
DvdItem.find(:all)
# find just box sets
BoxSet.find(:all)
# and just actual disks
Dvd.find(:all)

# with this approach you can even specify that individual disks are inside, or 'belong to', a box set
class Dvd < DvdItem
  belongs_to :box_set
end
class BoxSet < DvdItem
  has_many :dvds
end

# and you can do crazy stuff like:
BoxSet.find(:first).dvds.size # => 5
BoxSet.find(:first).dvds.first # => #<Dvd:234b233> (this means it's a Dvd Object


Will that work?

Re: Self referential relationship vs. separate model design question

Danger's suggestion would make it easier to select the values you want for the second condition. This way you can use the parent_id (or I guess it would be box_set_id) and type columns together to select the dvds you want.

Railscasts - Free Ruby on Rails Screencasts

Re: Self referential relationship vs. separate model design question

Thanks for the suggestions. The STI method looks interesting. (Though I'll give the "acts as a tree" approach a try as well.)

However I have a related question:

There is a HABTM relationship between collections and dvd/boxes.
However I'll probably add another layer of abstraction between the collection and the dvd/box as a join model, probably called CollectionItem. This would add owner specific data to the dvd/box (e.g. buy_date, rating etc.)

I was planing to try the new has_many :through feature.
E.g. Collection has_many dvds/boxes :through => CollectionItems...

Now based on the above suggestion the same should also work for "has many DvdItems through CollectionItems". But would it work for the derived classes?
E.g. has_many dvds :through => CollectionItems ?

Re: Self referential relationship vs. separate model design question

Here's the syntax for acts_as_tree:

class Collection < ActiveRecord::Base
  has_many :collection_items
  has_many :dvds, :through => :collection_items
end

And for the STI approach Danger mentioned (where DvdItem is the base class)

class Collection < ActiveRecord::Base
  has_many :collection_items
  has_many :dvd_items, :through => :collection_items
end

At least I think that's how it would go. Haven't really tested it.

Railscasts - Free Ruby on Rails Screencasts