Jump to content

Recommended Posts

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

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.

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

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"

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

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" :D 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 :happy-04:.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.