4

I have a nested table hierarchy, similar to the following one (this is an example but demonstrates the schema well enough):

+---------+  +---------+  +---------+  +-------------+
| users   |  | blogs   |  | posts   |  | comments    |
+---------+  +---------+  +---------+  +-------------+
| user_id |  | blog_id |  | post_id |  | comment_id  |
| email   |  | user_id |  | blog_id |  | post_id     |
+---------+  | name    |  | content |  | comment     |
             +---------+  +---------+  +-------------+

So, the object hierarchy is clear: a comment belongs to a single post, which belongs to a single blog, which belongs to a single user.

Now, in my app code, I want to verify that comments can only be read by the user who owns the blog which owns the post (assume these are private comments, ok?). E.g., when I SELECT * FROM comments WHERE comment_id = 666, I want to verify that the requesting user "owns" that comment.

My dillema is this: Would you advice that I:

  • duplicate the user_id to comments, so I always have a quick reference to the owning user, and checking the user id (during the select or later) is straightforward, or
  • Write my DB access code (a mapper or what have you) to always JOIN and fetch the user_id field as part of the returned object. Mind you that in this case I need two JOINs to do that

Basically I see it as performance vs. the so called bad practice of data duplication - but is it really such a bad practice in this case? It is unlikely that user_id values will ever change or that a blog changes its user_id.

I am using MySQL (MariaDB in fact) if that makes any difference.

  • Wow two joins on a key. Are you sure you have performance issue? – paparazzo Sep 20 '15 at 17:13
  • No, I'm not sure. I'm trying to figure out what people would see as a better practice. And yes, on big tables, two joins on a key can have a high impact on performance, at least in my experience. – shevron Sep 21 '15 at 5:07
  • Can? Do you currently have a performance issue or not? Premature optimization is not a good practice. – paparazzo Sep 21 '15 at 6:14
  • 1
    This is a design woe, not a request to solve a specific performance issue. If you think two joins are not a problem, just upvote the right answer. No need to downvote the question, I think its a valid one, even if it is not based on some actual pain I have right now. – shevron Sep 21 '15 at 10:23
  • 2
    I support @shevron. We are asking questions and looking for the answers here to enlighten ourself and those who stop by later, not to downvote something just because you do it different. Remeber: stupid question is better than no knowledge. – Dima Jul 25 '16 at 9:41
2

You should go with second option obviously for many reasons:

  • Will you add user id again if you need to come up with an entity at same level of comment? May be it won't happen in your scenario, but still.

  • Storage, as your database size grows

  • There won't be performance impact much as your query is highly selective and if you have good index design.

  • Well, the author makes a point that userId will never (or very rarely) change. So, while storage space is cheap, CPU speed is more important here. I wonder if anybody can test both approaches? – Dima Jul 25 '16 at 9:43
0

In my opinion you should go with the second way, that's the INNER JOIN. Since , in my understanding, this won't be a critical query to the database (read intensive) this approach would be the best, for simplicity in development and makes for "good practice". Other than that the comments could be many, and retaining an extra user id would add few bytes at a time to your database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.