jonniejoejonson Posted July 25, 2011 Share Posted July 25, 2011 Hey guys, please take a look... the end goal: I am trying to create a message system like facebook, so it is based on conversations rather than your standard messageing system: The way i see it working: table 1: messages_table: message_Id (auto inc - primary)(big int) poster_User_Id (big int) thread_Id (indexed)(varchar) message (text) table 2: recipients_table: thread_Id (varchar) recipient_User_Id (big int) opened (tiny int) Rather than having messages you will be able to have conversations based on a thread_Id. The thread_Id will be made up of the users_id's of the users involved in the conversation... for example 524_78765_992368765 would be a conversation between 3 users with the respective Id's... this way you will only be able to create one thread/converstion between a specific group of users. Questions: 1. What would be the primary key of table 2? 2. A sample thread_Id would be something like: 524_78765_8687655_9998125626... Seeing as i am having to group by this field in a lot of my queries would it be better to have this as a bigInt field, rather than a large varchar field?.. and if so how would i go about making it so that a group of users can only have one threadId... or does it make no real difference? 3. Are there ways in which you would improve this system?... Kind regards J Quote Link to comment https://forums.phpfreaks.com/topic/242729-facebook-style-messaging-system-based-on-conversations/ Share on other sites More sharing options...
Muddy_Funster Posted July 25, 2011 Share Posted July 25, 2011 1: As it stands there is no field there that would be viable as a primary key 2: Main difference would be that if you change it to an int field you loose the ability to have a seporator, in your case the underscore, that is anything other than numerical. As the rest of the information is also numerical you would have to make a complex number string as the delimiting refference which would be more detremental than good. 3: Lots. From the ground up it looks too clumsy - e.g. what happens when the same group of people want to start a second conversation without closing the first? How are you dealing with the possability of new people joining into an existing conversation, or one person dropping out while the others continue without them? - I would change from assigning members to a conversation and would rather assign conversations to members. I would have the thread table, having thread_ID as an indexed field (without much thought I would generate the id as a combination of creating memberID and currentDateTime) I would then use that to make a refference list of members that are associated with the thread. Include a status option for each member for things like "invited", "participant", "reader"...etc so that different statuses can be assigned to different members depending on what the creator member chooses for them. This lets members then "opt out" or "request invite" to conversations. Other than that the other changes I would make would be dependant on factors that you havn't provided information about in the post, You have a good idea, and it will run with the setup that you have, but I think it would be better (and make life down the raod much easier) with some changes. Quote Link to comment https://forums.phpfreaks.com/topic/242729-facebook-style-messaging-system-based-on-conversations/#findComment-1246707 Share on other sites More sharing options...
jonniejoejonson Posted July 25, 2011 Author Share Posted July 25, 2011 Hey thanks alot for the reply... I can see you're points, however i was trying to stop users from being able to create new threads... so if you are in a conversation between a group of users, you cant create a new thread between that same group of users... a users only option like with the 'new' facebook messages is to open the conversation and archive it... if someone adds to the conversation it will mark the thread/conversation as open again for all the users... As you stated my structure would work, however my real concern is still with the thread_Id... because if there are 20 users in a conversation my threadId is going to be massive as it will consist of 20 user_Id's in ascending order joined by underscores.... but this is the only way that I can think of creating a threadId that would prevent the same set of users from being able to create a new thread, becuase whatever the message, if it is the same group of users it will always have the same thread_Id... Is there another way around this?... Thanks again for your help... Kind regards J Quote Link to comment https://forums.phpfreaks.com/topic/242729-facebook-style-messaging-system-based-on-conversations/#findComment-1246720 Share on other sites More sharing options...
Muddy_Funster Posted July 25, 2011 Share Posted July 25, 2011 the threads table I suggested would allow you to limit conversations to singular events per user group - the way you want to do it - and give a limited threadID. example: Existing User Table - take the uID Create a new "threadStatus" table: threadID - Custom Genrated as mentioned in last post Status - int/bool/enum (whatever you like) - indexed, normal Create a new "threads" table Fields: recordID - int, auto inc, PK threadID - ForignKey index Generated In ThreadStatus table set with "On Update - Cascade" uID - taken from the Users table - indexed, normal When a conversation is started you make a new record in the threadStatus table with just the threadID. Next Update the threadStatus Seting it to whatever you want to use for "open". Then you make a new record with the same threadID for each uID that is part of the conversation in the "threads" table. This ties all the users to a single manageable threadID without needing to delimit the contents of the field with underscores. I have put that theadStatus.status and threads.uID are indexed as I assume at some point you will want to see "how many users are in an open conversation" or "how many conversations is user x currently taking part in" Quote Link to comment https://forums.phpfreaks.com/topic/242729-facebook-style-messaging-system-based-on-conversations/#findComment-1246736 Share on other sites More sharing options...
jonniejoejonson Posted July 25, 2011 Author Share Posted July 25, 2011 Thanks Muddy_Funster for your continued support, On a slightly different note... if I were to build the message system like you suggested, and I had a reasonable dedicated server to run it on... how many messages do you think I could store and it still satisfactorily run queries that join tables and select the most recent messages within a thread?... Thanks again for your help... kind regards J Quote Link to comment https://forums.phpfreaks.com/topic/242729-facebook-style-messaging-system-based-on-conversations/#findComment-1246810 Share on other sites More sharing options...
Muddy_Funster Posted July 25, 2011 Share Posted July 25, 2011 a few hundred thousand shouldn't put any noticable strain on resources as long as everything is indexed properly (and as long as you never use SELECT * of course ) Quote Link to comment https://forums.phpfreaks.com/topic/242729-facebook-style-messaging-system-based-on-conversations/#findComment-1246824 Share on other sites More sharing options...
jonniejoejonson Posted July 25, 2011 Author Share Posted July 25, 2011 Thanks again Muddy_Funster, So what would you need to do for it to be able to handle millions of messages?... is it just a matter of hardware?... or is mysql just not built for that kind of scale?... kind regards J Quote Link to comment https://forums.phpfreaks.com/topic/242729-facebook-style-messaging-system-based-on-conversations/#findComment-1246878 Share on other sites More sharing options...
Muddy_Funster Posted July 26, 2011 Share Posted July 26, 2011 Just a matter of hardware, MySQL is probably the most scalable DB format available at the moment. Quote Link to comment https://forums.phpfreaks.com/topic/242729-facebook-style-messaging-system-based-on-conversations/#findComment-1247181 Share on other sites More sharing options...
jonniejoejonson Posted July 26, 2011 Author Share Posted July 26, 2011 Thanks Muddy_Funster. Kind regards J Quote Link to comment https://forums.phpfreaks.com/topic/242729-facebook-style-messaging-system-based-on-conversations/#findComment-1247206 Share on other sites More sharing options...
fenway Posted July 26, 2011 Share Posted July 26, 2011 Just a matter of hardware, MySQL is probably the most scalable DB format available at the moment. That's a rather bold statement -- but for millions of rows, it probably doesn't matter. Quote Link to comment https://forums.phpfreaks.com/topic/242729-facebook-style-messaging-system-based-on-conversations/#findComment-1247314 Share on other sites More sharing options...
Muddy_Funster Posted July 26, 2011 Share Posted July 26, 2011 Just a matter of hardware, MySQL is probably the most scalable DB format available at the moment. That's a rather bold statement -- but for millions of rows, it probably doesn't matter. I know...that's why I used "probably" ant for the record it is baised purely on personal experience using MSSQL, Oracle and MySQL on a variety of different hardware setups, so don't take it as being anything close to official, accurate or anything else that could get me a law suit . Quote Link to comment https://forums.phpfreaks.com/topic/242729-facebook-style-messaging-system-based-on-conversations/#findComment-1247322 Share on other sites More sharing options...
fenway Posted July 26, 2011 Share Posted July 26, 2011 Agreed -- I would have said "easily scales to millions of rows". After all, facebook uses it. Quote Link to comment https://forums.phpfreaks.com/topic/242729-facebook-style-messaging-system-based-on-conversations/#findComment-1247350 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.