CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
bratao
bratao

Reputation: 2080

How to get related items without SELECT each id

I have to do a database design for a Friend-Network. When I add someone as someone friend, I actually add their uniqueID to a table with two columns.

| USER 1 | USER 2 |

So when I want to known what are someone friends, I do a SELECT in this table looking for ID.

But the problem, is when I want to show the friend list i have to search all his friends, and after do a lookup for each ID looking for details. It just does not look right.

Is there a more efficient way to do it? Could I design my database? Is there any place where we can learn more about database design?

Upvotes: 0

Views: 129

Answers (2)

dfb
dfb

Reputation: 13289

I think you're on the right track given what you've said above. A many-to-many association table is the canonical way to what you're describing. It sounds like the issue might be in your query - you say you have to make multiple lookups. You should be able to write something like this

SELECT * FROM People p1
         INNER JOIN Friends f on p1.id = f.id1 and p2.id = f.id2
         INNER JOIN People p2 on p2.id = f.id2
       WHERE p1.id = 6123

Upvotes: 1

Sabeen Malik
Sabeen Malik

Reputation: 10880

Your design seems fine to me. However look into JOINs which will help you get rid of nested queries to get info for the users. JOINs will allow you to get all the info about both users in one query and then you can loop through and print the list.

The query might become something like:

SELECT 
     u.name 
FROM 
     friends f 
     JOIN users s ON (u.id = f.user_id_2) 
WHERE 
     f.user_id_2 = 1234

Upvotes: 1

Related Questions