CODEWITHSUNDEEP
mysqlsqldatabasejoindatabase-design
Uiot
Uiot

Reputation: 83

Should I save the same value(id) into tables or join them to retrive the value(id)?

Let's say I have a table posts that contains the User_id of the user who posted and the post post_id of the post. And I have another table comments that contains only the post that it belongs to child_of_post.

The problem is here: I need to select only from comments but at the same time get the user_id of the post that the comment belongs to.

So should I use a join like:

SELECT user_id FROM comments INNER JOIN posts ON child_of_post  = post_id

Reading this confused me even more, I don't really know how to explain it, but, in general if I need to use the same value like and id, should I save that value in every table that I need it ? Or should I save it only in one table and use joins to retrieve it ?

Is using a join better that adding one more column to a table ?

Upvotes: 1

Views: 60

Answers (1)

GMB
GMB

Reputation: 222582

Is using a join better than adding one more column to a table ?

In general : Yes.

Your database design looks good. As a general principle, avoid duplicating data across tables. This is inefficient in terms of storage, and also can quickly turn into a maintenance nightmare when it comes to modifying data, which ultimately threatens the integrity of your data.

Instead of duplicating data, the usual approach is to store a reference to the table row where the original data is stored ; this is called a foreign key, and it offers various functionalities that help maintain data integrity (prevent inserts of orphan records in the child table, delete child records when the parent is deleted, ...).

In your use case, you indeed would need to JOIN to recover the user that created the original post, like :

SELECT p.user_id, c.* 
FROM comments c
INNNER JOIN posts p ON c.child_of_post  = p.post_id

Assuming that post_id is the primary key of table posts, such JOIN with an equality condition referencing the primary key of another table, is very efficient, especially if you create an index on referencing column comments.child_of_post.

PS : it is a good practice to give aliases to table names and use them to index the fields in the query ; it avoids subtle bugs caused by column name clashes (when both tables have fields with the same name), and makes the query easier.

Upvotes: 1

Related Questions