MySQL table structure ?

[Original Poster]

I just wanted to get some opinions on how i should lay out a table ... I Have made a chat system which requires loging in ... that all works but now I want to add friend feature... although I have built a table for "friends" before I am not 100% sure that mine is efficient ... the best I have come up with so far is setting one column as the person who sent the friend request and the other as the acceptie .... any improvements on logic ? I was thinking possibly an array of user IDs within each users "friend" column ... but not sure if that would just be to much I think it would be ... opinions ?

[arlen2411]

I am not sure if this is the best solution but your table friends could have fields like this:

me_id

friend_id

accepted (bool)

So if you send an invitation to a friend, you know how to set IDs. If some one send you a request, then he will put his id as me_id....And when you are doing a SELECT to get you friends you can do sth like this:

SELECT * FROM friends WHERE me_id = x

SELECT * FROM friends WHERE friend_id = x

where x is your id

Then put return valuse to one table.

[Original Poster]

that is somewhat what I was thinking ... but hmm still seems like that table would be more then it needs... but I guess its best option ..

[arlen2411]

Probably there is a better way for this, but I am not a DB specialist. This is the way I would do it.

[n_K]

I can't really work anything out without seeing your full table structure and what it is you're trying to do but remember, you want to normalise your database as much as possible to make it more efficient, so add linking tables and whatnot.

[Tuishimi]

I'd probably do the same as arlen... Do you want to provide any message with the invite? If you think the table will grow huge, I'd index both ID's... Tho' it probably isn't really THAT necessary since both fields will be integers and any number of internal DB sorting algorithms will probably handled queries easily.