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 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.
