I'm not good with diagramming tools or ascii art, so here's a hand-drawn diagram of what I have:
In short, I have what can loosely be described as a scavenger hunt app. A game has several objectives, and players can attempt the objective, comment on the objective, share hints, share images, etc. Each of these actions have different schemas (An attempt has a verification_flag, a comment has comment_text, an image has an image_url, etc).
In my app, on the "Objective" dashboard, I want to show an activity feed for that objective. How can I best do this?
For the schemas described in the badly drawn diagram, I was thinking something like this:
SELECT * FROM activities
LEFT OUTER JOIN images on images.activity_id = activities.activity_id
LEFT OUTER JOIN comments on comments.activity_id = activities.activity_id
LEFT OUTER JOIN links on links.activity_id = activities.activity_id
LEFT OUTER JOIN attempts on attempts.activity_id = activities.activity_id
LEFT OUTER JOIN hints on hints.activity_id = activities.activity_id
WHERE activities.objective_id = 123
ORDER BY activities.created_at DESC
...and when the app receives the results of this monstrosity, it will somehow go row by row, figure out which class the row represents (oh, this row, is it an Image, Comment, Attempt, Hint, etc?) then render the appropriate template for the browser.
I've read about the "exclusive arc" pattern and that it should be avoided, but it is unclear how to avoid it. So for this problem, can I please get some specific guidelines or pointers in how to do this correctly?
