Retrieving "likes" tied to users from a database

Question

I'm new to database structure. I'm trying to create an app that allows users to like certain entries, but I want to be able to tie likes to users so that I can change the visuals before/after the like action.

I think from research that I should have an 'entries' and 'users' table and then have a 'likes' table that ties the two to each other.

The only thing I'm unsure of is, when getting and displaying the contents... how would I write the queries? If I query for all the entries I need, do I then go back and individually query each to see if it has a like tied to it for the current user? That seems like it might be a costly operation. Is there a more efficient way?

Hope that makes sense, Thanks.

Answer 1

I think you have the right database design in mind. As far as queries are concerned, assume tables as such:

Users

ID | Name
1  | Bob
2  | Sally

Entries

ID | Name
1  | Red
2  | Blue
3  | Yellow

Likes

UserID | EntryID
1      | 1
1      | 2
2      | 2
2      | 3

So we can say Bob likes Red and Blue while Sally likes Blue and Yellow. So a query to retrieve all entries, plus an indicator of what Bob likes would be:

SELECT 
e.ID,
e.Name,
l.UserID
FROM Entries e LEFT JOIN Likes l ON l.EntryID = e.ID
WHERE l.UserID = 1 -- Bob's User ID
ORDER BY e.Name

This would return

ID | Name   | UserID
2  | Blue   | 1
1  | Red    | NULL
3  | Yellow | 1

The UserID column indicates if Bob likes the entry or not - a NULL is No and a value is Yes.