What tables do I need?

You would simply need a users table and a messages table. You would probably want to implement some sort of limit on the number of messages a user can have total, and that would free up space on the server. Don't use the userID as the primary key, but have a unique ID for each message, and then have a to and from column where the receiver's userID and senders userID would be entered to identify when reading the messages.

This is how I would do it. Whether or not it's the best method, I am not sure.

No, they do not really need any connection. The users table, generally, presents an environment for profile information and it gets us a userID. Then, the messages table handles all the messages, using the userIDs as the to and from fields, which you would certainly want to present as usernames in the messages.

What about if I want a freinds table? What columns would I have? Would it work the same as the messages table?

I assume by friends you mean like a buddies list, and with this there are a few ways you could do this. You could either create a new table for each user called something like 'userName_friends', which obviously would result in a lot more data and would slow down your system most likely. Another method would probably be to add another column to your table and set it as a SET (as opposed to VARCHAR, INT, etc). You could separate each friend with a comma and then use JavaScript or possibly PHP (I can't remember right at this moment how to do it) to strip the commas and store each friend separately in an array. I'll get back with you if I think about it, unless someone else does first.

Thanks! I have just thought that if people are to request to be friends there is going to need to be a pending friend request table?

Yes, although it doesn't have to be a table, you do realize? It can be just another column in the table, each friend separated by a comma. Then, once approved, the corresponding request would be deleted from the pending column and added to the friend column.

I'm not sure what the FriendsWith column means in the Pending Friends Request Table, but here's how you could put it into one table:

UserId, FriendsWith, Pending

and if someone happens to stumble upon this thread who can tell you how to use MySQL and arrays that would be great, but I'll do a little research today and see what I can figure out.

Here's what I would suggest, although I'm still not completely sure how to do it:

The users table looks like this:

UserId, Username, Name, Email, Location, CurrentFriends, PendingRequests

Obviously those names can be different, but just so you know what each is for.

Here's what one row might look like:

321
mscreashuns
Andrew
[email protected]
USA
(friendID1,friendID2,friendID3,friendID4...)
(pendingFriendID1,pendingFriendID2...)

Then each value in the PendingRequests and CurrentFriends columns could be separated starting at the commas and ending at commas (Like I said, I'm still working on exactly how you could go about this. I have several ideas in mind). By the userID, the usernames would each be listed as a link saying 'Add USERNAME as a friend.' and when you click that, it removes that userID from the PendingRequests and adds it to the CurrentFriends.