Would a "links" table be the ideal solution for these situations?

[eluser]kyleect[/eluser]
Things like tags, friends, categories... They all have a field that can have multiple answers. An item can have many tags. A user can have many friends. I was thinking of using a table that links one field to another as a solution. For example

user1 id=1
user2 id=2
user3 id=3
user4 id=4

The links table would have two fields. user_a and user_b. It would look like this

user_a: 1; user_b: 3
user_a: 1; user_b: 4

user1 is now friends with user3 and user4

[eluser]Michael Wales[/eluser]
Yep - they are commonly referred to join tables and are the perfect solution to this problem.

[eluser]darkhouse[/eluser]
I've often wondered about this for a 'friends' scenario. Categories, tags, etc, no problem because you're usually linking up things like product_id and category_id. My issue has always been linking up 2 users (so far I haven't had a project that required this, but I can see it coming up soon) and then grabbing data from those users.

If you use the user_a and user_b approach, this keeps the table as lean as possible, but I just can't seem to wrap my head around how you join data from the users table in a single sql statement, like to get a list of your friends.

If you do something like user_id and friend_id, now you'll have 2 rows for each 'friendship' but it would be very easy to write sql to join whatever data you want.

Any suggestions?

[eluser]TheFuzzy0ne[/eluser]
I'd just like to point out that the design of the join table may be flawed. It only appears to work one way. For example, user 1 may be friends with users 3 and 4, but it doesn't look like they are friends with him. Is that right? I'd be quite concerned if any of my friendships were like that.

Basically, you'd probably have to run 2 queries, one to query the left-hand column, on for the right, and then you'd need to filter out any duplicate entries. Otherwise you'd need to add two rows for each user. For example, if you wanted to make user 2 and user 4 friends, you'd add (user 2, user 4), and the reverse (user 4, user 2).

EDIT: Sorry for repeating darkhouse. I had no idea he'd already suggested the idea.

[eluser]kyleect[/eluser]
I could just query for that user in all relationships. So

Code:

SELECT * FROM `friendships` WHERE user_a = user_1 OR user_b = user1

I haven't tried this at all, just planning my work out.