mscreashuns — Thu, 26 Jul 2007 12:30:02 +0000

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
name@domain.com
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.

drew22299 — Thu, 26 Jul 2007 09:11:30 +0000

What fields would you use for the friends and pending table? Would I have the following in the friends table?

UserId, FriendsWith, Pending

The FriendsWith Column is the user who sent the friend request, and before they are approved 'Y' is put into the pending column.

If I had a friends table rather than a friends table for each user (which would take up alot of space) would the data look something like this:

UserId, FreindsWith, Pending
user1 user4
user1 user2
user1 user11 Y

User1 has 3 friends, but there are three records, is this why you suggested using the array in the users table?

mscreashuns — Wed, 25 Jul 2007 12:37:00 +0000

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.

drew22299 — Wed, 25 Jul 2007 10:30:04 +0000

If I have a pending and friend coloumn wouldn't that mean I could only have one pending friend request at any given time? Because wouldn't new friend requests just overwright an existing pending friend request? I'm thinking I will need to have separate tables for now, and maybe change it later if needed as you suggested using an array to store friends.

I am going to create these tables now and intergrate the messages and friend requests into my website, please can you help with the tables?

So, assuming I already have a users table with the following fields: UserId, Username, Name, Email, Location, (any additional fields you think would be needed please let me know)

Messages Table

I'm not sure on how to impletemnt the website to allow messages to be sent between users but here are the fields I think I need (does this look right to you?)

MessageId, Message, DateSent, UserFrom, UserTo

Friends Request Table

For this I am going to put a link on the users profile to allow them to send a friend request to that user. Would I need a pending table and a friends table for this? Or could it be done in just one table?

Pending Friends Request Table

UserId, FriendsWith, Pending,

Friends Table

UserId, FriendsWith

mscreashuns — Wed, 11 Jul 2007 16:07:06 +0000

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.

drew22299 — Wed, 11 Jul 2007 15:15:04 +0000

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?

mscreashuns — Wed, 11 Jul 2007 13:41:07 +0000

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.

drew22299 — Wed, 11 Jul 2007 10:45:47 +0000

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

mscreashuns — Mon, 09 Jul 2007 16:46:18 +0000

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.

drew22299 — Mon, 09 Jul 2007 15:05:35 +0000

Thats a really good way of doing it! Thanks But would I need a message ID column in the users table? Does the user table need to know about the message table?