Very helpful, thanks Jeremy! > On Sep 8, 2016, at 10:26 PM, Jeremy Evans <[email protected]> wrote: > > On Thursday, September 8, 2016 at 6:35:16 PM UTC-7, Andrew Burleson wrote: > 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? > > A scalar primary key is faster, but a composite primary key is unlikely to be > a bottleneck in most queries. I would do whatever makes maintenance of the > program easier, and only worry about optimization if you need to. > > 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? > > Sequel has supported composite key associations for many years (e.g. > :key=>[:chapter_id, :user_id]), so you shouldn't run into any problems. The > only time it could be problematic is if you are using filtering by > associations or using dataset associations and your database doesn't support > composite values for the IN operator (e.g. "(a,b) IN (SELECT c, d FROM t)"). > In that case, Sequel has to emulate things, and that emulation could be slow > or not work. > > 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? > > up do > drop_constraint :primary_key_constraint_name # PRIMARY KEY is a type of > constraint > add_primary_key [:chapter_id, :user_id] > end > > down left as an exercise for the reader. :) > > Thanks, > Jeremy > > -- > You received this message because you are subscribed to a topic in the Google > Groups "sequel-talk" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sequel-talk/A4QqwWxYsuU/unsubscribe > <https://groups.google.com/d/topic/sequel-talk/A4QqwWxYsuU/unsubscribe>. > To unsubscribe from this group and all its topics, send an email to > [email protected] > <mailto:[email protected]>. > To post to this group, send email to [email protected] > <mailto:[email protected]>. > Visit this group at https://groups.google.com/group/sequel-talk > <https://groups.google.com/group/sequel-talk>. > For more options, visit https://groups.google.com/d/optout > <https://groups.google.com/d/optout>.
-- 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.
