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!
Last edited by bjavor (2007-01-10 17:34:55)