So I'm wondering about something which is half a Sequel question and have a Postgres / SQL best practices question:
I've got tables now like this: Chapter: id, author_id, text ... Feedback: id, user_id, chapter_id, text ... Originally users could post multiple "feedback" on a chapter. However the system has evolved such that this is no longer a good idea, it will work better to constrain feedback to be unique per user, chapter combination. So I would plan to add a unique(:user_id,:chapter_id) constraint on Feedback, but in that case that combination could be used as the primary key. This has me wondering several things: 1) What are the benefits / tradeoffs of referencing a row by a composite key instead of a primary key? The primary key isn't really being used since I would always look up feedback by user, chapter, or the combination, so dropping it would theoretically free up some space. But is there any other performance gain or penalty, particularly in terms of how Sequel works, that I should know about? For example, let's say that I add "replies" to feedback. The replies now need to reference the combination of user_id + chapter_id to have a foreign key to a particular feedback. Is that more expensive, or does Sequel have gotchas supporting that kind of association? 2) Let's just assume I've already collapsed the duplicates so that there is never more than one unique Feedback for each chapter_id, user_id combination. How would you write a migration to drop the id primary_key and how would you add the new primary key constraint on the chapter_id, user_id combination? Sorry to ask something so broad, I'm not a database expert so I'm not sure how to narrow the question down better than this. Thanks, Jeremy for your work on this project, it's really amazing! Andrew -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
