I hope everyone enjoyed their holidays.

Problem:I have an email system that attempts to be threaded. I am now trying to work out how to do replies to emails that have certain users removed from the "reply to". Example, John sends email to Tom, Lee and Harry. But Harry decides to reply to all except John. This is my messages table:

http://img227.imageshack.us/img227/3697/dbtablexg3.jpg

My Solution: I was thinking of putting a (0) infront of threadIDs for those messages that have had some users removed from the reply. This way the old thread can continue but the new thread will also contain the old thread but only viewable by a subset of the receivers of the old thread.

I will have to use PHP logic to work this out rather than my DB doing the work for me.

Is this a good idea? Is my current DB structure good enough to do this? Is it better to start with a new DB schema to cater for this feature.

That sounds like a messy solution - couldn't you just have a table with all the emails for a given response - or if you want to save space etc then you could design it so that if a reply has no email addresses attached then it should use all the email addresses for the original (or the email next up in the hierarchy) email. Remember that some email addresses will be normal, some will be cc and some might be bcc.

Of course you could save yourself a great deal of effort and just use yahoo, gmail, hotmail etc but if you really feel your mousetrap is going to be way better than these existing tools - good luck

That is what I was afraid of.

I forgot to show you my other table. Here is how the messages table and receivers table are linked (image attached). All messages are stored in the messages table. A record is made in the receivers table for every reciepent.

The problem is, how to, in my current setup, include replies that do not include all of the initial receivers? I also completely forgot about cc and bcc, I am thinking about how to do that too.

You are assuming I am making an email service for people to use? No, its just part of a bigger system

Just have a type field alongside each email address saying whether it is cc, bcc or normal.

Yes and no - I assumed you were making an email system but no I didn't think people would use it. The difficulty you face is that most of the current systems offer loads of facilities (including cc etc) and are absolutely free.

You're going to redesign the internet as well? I honestly love to hear about brave new projects but it's difficult to spot why you're doing this from what you've said so far.

Ah got it! I still can't figure how to cater for replies to messages that does not include all the receipents from an old message with my current structure , any ideas?

The problem, is that messages are threaded like a forum and anyone who has the threadID can see new posts to the thread. I want to hide it from some since its a continued conversation between a private set.

I am in no way competing with the current mail service providers! Its just a feature of my system.

It is a new brave project, yes! Its one of my new hair brained schemes to get rich quick! But i got stuck at the DB design lol

Assuming that you have control of the code that displays the messages, just have it check to be sure that the current user is on the "permitted list" for this message.

-PatP

Thanks for the reply PatP. This will work for registered users.

However, there is an area in my web application where you don't even have to login in to view your emails. Because part of my system uses a long unique URL variable to pull up messages. Before people start saying "your crazy" (prolly true) - it is a very unique and long and unguessable identifier.

I need Thread2 to be linked to Thread1 in some way, I can't figure out a way to do that with my current DB.

Anyone want to see a more detailed version of my DB structure?

Maybe I'm missing something important, but I'd take the easy approach. Tag each message in the thread with its own list of approved viewers. Only show the message to logged in users that are on that message's approved list.

-PatP

My explanations aren't very good. Let me do it properly:

Messages are linked via a threadID, a bunch of messages linked by a common threadID, which is usally the message ID of the first message, is called a thread in my system.

The problem is that when some users are removed from a conversation half way. Some people will decide to continue their own conversation without some people. This presents a problem for my system.

I need a way for removed users not to be aware of a continued conversation. So the linking of messages via a threadID has to be stopped at the point some people decide to continue a conversation by themselves.

Also removed users should still have access to the old thread. Which itself can be continued.

Basically branching out a (mother) thread. The sub (child) threads will contain the (mother) original thread to allow for continuity.

How can I achieve this with my current Db strucutre, its partially working.

I'm still a bit befuddled from last nights celebrations but this is my attempt at the database structure you'd need :

Code:

thread: id, user_id
msg: thread_id, seq_id, subject_txt, msg_txt, sent_date, parent_seq_id
msg_user: thread_id, seq_id, user_id, recipient_type
user: id, name, email, password, join_date
recipient_type: name (ie cc, bcc etc)

Each new msg would get given a new thread_id and then stored in the msg table with an initial seq number of 1. All the recipients for this message would get put in the msg_users table along with the type of recipient they are. All replies would keep the same thread_id with the 1st reply having a seq number of 2 and the parent_seq number being 1. The message recipients would just be copied from the parent list of users (unless there's been a change) and stored in msg_users with the original thread_id and the new seq number.

If at some later point you want to save space you could say if there are no msg_users for a given thread_id/seq_id then use the msg_users from the parent thread - this might need to be repeated up the chain - but personally I'd leave this out until you find you need to save space.

Using a long url as a security method offers no security at all. You'd do better using session ids from PHP.

Must admit I still think you get pretty much the same functionality from a web based email system ie you can change the distribution list at any point and those people removed from the list won't see the following emails, you get security, etc etc. Can you explain where you offer more functionality?

I haven't disregarded this thread, I am drawing up your structure, seeing how it works and trying to incorporate it in mine, if not possible, I will use your design since my db design sucks!

You mean using the session_id as part of the URL? Thats a good idea, I can use it to make it extra long. Currently my URL is in the format:

de4fb9659cadfd97232779a2b4b6f1909c115524138307 - can you guess what it is?

This is the secret sauce but within a few weeks I will be able to give you a URL of my project so you tell me its useless.

Eureka! That line really helped me, thanks mike! What I was missing from my table was another field, that would link it to a previous conversation and I was just searching all matching threadIDs. But your idea of sequence means that I can search a thread and then look for threads it extended from! How does this look? (attached)

Maybe I should remove the seq_id from the messages table and put it in its own table with msg_id.

Best to test the db on paper first. Try and use sprocs to add and get data so that if you do change the db then you won't need to alter your code too much.

I mean not passing any security stuff as part of the url and using session ids to hold this type of data.

I assume that's the parameter for your program rather than the URL itself but it looks like an md5 encryption of a set of parameters. In any case I wouldn't need to break the encryption as I'd presumably just enter the above and it will take me somewhere - I could then follow the links to get more info.

I'm sure we'll all love to see it. If it proves to be successful then fantastic and if not then at least it will be a great learning experience. Best of all you will realise ,that having followed the process of developing an idea into an end product, that you can achieve anything with enough time and effort.