I wonder if in this age of "community" other people here are considering ways to store relationships. I have something to have created for a client and before I just send it off to the DBA guy I'm curious to try and grasp some level of understanding of efficient ways to do this.

Let's say there are 4 users:-

Bob
Jack
Tim
Samantha

And let's say that:-

Bob and Jack are friends
Jack and Samantha are friends
Tim and Samantha are friends.

The issue is how to store this and retrieve from it efficiently. Friend relationships here are bi-directional/mutual.

Gut instinct says the starting point for this is two tables, one for users and one for friends:-

MySQL
- Users Table
- - UserID, Username

- Friends Table
- - UserID, FriendsUserID

With this structure, the Friends table entries might look like this (I'm using usernames rather than ID's here for clarity of example):-

MySQL
- Friends Table
- - Bob, Jack
- - Jack, Samantha
- - Tim, Samantha

To retrieve Jack's friends from this table, I'd need to check across two columns (so each needs to be indexed for speed):-

SELECT FROM FriendsTable WHERE UserID = "Jack" OR FriendsUserID = "Jack";

..... and as soon as I get to that point my gut instinct says this is wrong (I'm not sure why - lack of elegance perhaps).

Is it wrong? Will it give scaling headaches? Is there a nice clean and elegant way of storing this type of MANY->MANY relationship?

I found a great page on the net called Rules of Normalisation:-

[datamodel.org...]

.... but that's way over my head in terms of detail. Really I was looking for a high level schema of what the tables and queries might look like so that I can't be BS'd by a database guy.

Any thoughts and experiences would be much appreciated.