I'm totaly lost; I read a lot of tutorials and forums, and I'm still not sure if my design is OK, so I decided to ask here. I'm sure it's not big deal for somebody with more expirience than me (approx 0 ).

What troubles me is this: I have users, and users can have friends. Any user can have any number of friends, but you first must agree to be somebody's friend. Users are neatly placed in a table and have their UserID (primary key) + usual stuff (email, SHA1 of password etc). So how do I define user's friends and requests for friendships? My idea was that each user gets a table, with name UserID_friends and 2 fields: friend ID (FK) and status, where status codes determine their relationship (eg. 1 - "I requested friendship", 2 - "he is my friend", 4 - "he requested friendship", 8 - "I am his friend"; using bitwise OR I can get any combination of these). Name of that table is stored in users table under field "frends_table".

Is this OK? If not, please give me advice, or point me to an article, I want this database to be as fast as possible, as I believe it might get a lot traffic if I'm lucky enough that my application gets enough traffic.

Thanks a lot for your help.

Each user shouldn't have his/her own friends table because the number of tables would multiply very quickly that way. You could make one table called friends and then have PK (AutoNumber) Owner (UserID) Friend (UserID) and then a status code(the 1,2,4 or 8 etc.), and a datestamp. That way, if you decide to add a status code, or change a status code you can do a global change on one table instead of having to write the code for each individual table that was user coded. You are going to also want to add an ignore table with the same kind of coding, so that it shuts certain users out of all the specific UserID content. 'Net people are funny, and sometimes need the ability to shut out stalkers and irritations.

If your app takes off, you are not going to want to have so many tables to clean up. Keeping it as simple as possible with good database engineering is a must.

If you want to take a look at a good MySQL driven 'user type' system that has good database structure, I'd take a look at simplemachines.org. Their forum software (open source) is really well designed.

Thank you for your answer and the link. I can't believe I didn't think of it myself..

Seeing how I had a completely wrong idea, I just have to check if something else has reasonable concept:

User A wants to send user B a message. This is what script does:

1) it checks if user A is allowed to send messages to user B
2) if he/she is, script adds message to messages table ( MessageID(PK), SenderID, RecipientID, message_body, timestamp )
3) when user B logs on, script checks if there are messages for him/her waiting, and displays message from user A

Logic looks fine to me. After you write the sproc to implement it, post it here for review.