What tables do I need?

They have: 105 posts

Joined: Mar 2006

Hello,

I am designing a website where users can register details, browse members and send messages to other members.

What tables do I need for this? Would I just have a users table and include all the fields in that? Or should I have a separate messages table which has the userID as a primary key? What if the user has lots of messages? Please can someone help if they know what to do!

Thanks,

He has: 698 posts

Joined: Jul 2005

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.

Kurtis

They have: 105 posts

Joined: Mar 2006

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

He has: 698 posts

Joined: Jul 2005

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.

Kurtis

They have: 105 posts

Joined: Mar 2006

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

He has: 698 posts

Joined: Jul 2005

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. Wink

Kurtis

They have: 105 posts

Joined: Mar 2006

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?

He has: 698 posts

Joined: Jul 2005

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.

Kurtis

They have: 105 posts

Joined: Mar 2006

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? Confused

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

He has: 698 posts

Joined: Jul 2005

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.

Kurtis

They have: 105 posts

Joined: Mar 2006

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?

He has: 698 posts

Joined: Jul 2005

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.

Kurtis

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.