How to handle ids and polymorphic associations in views if compound keys are not supported?
- by duncan
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?
