How to handle ids and polymorphic associations in views if compound keys are not supported?

Question

I have a Movie plan table:

movie_plans (id, description)

Each plan has items, which describe a sequence of movies and the duration in minutes:

movie_plan_items (id, movie_plan_id, movie_id, start_minutes, end_minutes)

A specific instance of that plan happens in:

movie_schedules (id, movie_plan_id, start_at)

However the schedule items can be calculated from the movie_plan_items and the schedule start time by adding the minutes

create view movie_schedule_items as
 select CONCAT(p.id, '-', s.id) as id,
   s.id as movie_schedule_id,
   p.id as movie_plan_item_id,
   p.movie_id, p.movie_plan_id,
   (s.start_at + INTERVAL p.start_minutes MINUTE) as start_at,
   (s.start_at + INTERVAL p.end_minutes MINUTE) as end_at
 from movie_plan_items p, movie_schedules s
 where s.movie_plan_id=p.movie_plan_id;

I have a model over this view (readonly), it works ok, except that the id is right now a string.

I now want to add a polymorphic property (like comments) to various of the previous tables. Therefore for movie_schedule_items I need a unique and persistent numeric id.

I have the following dilemma:

• I could avoid the id and have movie_schedule_items just use the movie_plan_id and movie_schedule_id as a compound key, as it should. But Rails sucks in this regard.
• I could create an id using String#hash or a md5, thus making it slower or collision prone (and IIRC String#hash is no longer persistent across processes in Ruby 1.9)

Any ideas on how to handle this situation?

Answer 1

I think you've built your models a little too complex. MoviePlan is a pattern that you use for every day's schedule of movies? I'd very much recommend to drop the MovieSchedule and instead allow the user to copy an existing MoviePlan. This will a) make your design simplier and b) allow more freedom for the user as schedules often differ in reality. An extra matinee on Saturdays, a special foreign movie on wednesdays, that's all what I see at my favourite cinema.

Lastly, making comments on a movie of a specific movie plan and schedule doesn't make any sense to me, or is this some customer management thing like "my seat in the 7pm show of Funny Movie was dirty"?