Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2007
    Location
    London
    Posts
    117

    Changing DB Structure for new feature or stick with current?

    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.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by compsci
    I was thinking of putting a (0) infront of threadIDs for those messages that have had some users removed from the reply.
    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

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by mike_bike_kite
    That sounds like a messy solution
    That is what I was afraid of.

    Quote Originally Posted by mike_bike_kite
    couldn't you just have a table with all the emails for a given response
    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.

    Quote Originally Posted by mike_bike_kite
    Of course you could save yourself a great deal of effort and just use yahoo, gmail, hotmail etc
    You are assuming I am making an email service for people to use? No, its just part of a bigger system
    Attached Thumbnails Attached Thumbnails dbdesignve7.jpg  

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by compsci
    I also completely forgot about cc and bcc, I am thinking about how to do that too.
    Just have a type field alongside each email address saying whether it is cc, bcc or normal.

    Quote Originally Posted by compsci
    You are assuming I am making an email service for people to use?
    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.

    Quote Originally Posted by compsci
    No, its just part of a bigger system
    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.

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by mike_bike_kite
    Just have a type field alongside each email address saying whether it is cc, bcc or normal.
    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.


    Quote Originally Posted by mike_bike_kite
    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.
    I am in no way competing with the current mail service providers! Its just a feature of my system.

    Quote Originally Posted by mike_bike_kite
    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.[/INDENT]
    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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by Pat Phelan
    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.
    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.

    Code:
    More Info: 
    
    Thread1 = Tom, Lee, John (people who can view thread)
    
    Thread2 (includes Thread1 contents) = Tom, Lee (people who can view thread)
    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?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by Pat Phelan
    Maybe I'm missing something important

    Quote Originally Posted by compsci
    However, there is an area in my web application where you don't even have to login in to view your emails.
    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.

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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?
    Last edited by mike_bike_kite; 01-01-09 at 13:40.

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    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!

    Quote Originally Posted by mike_bike_kite
    Using a long url as a security method offers no security at all. You'd do better using session ids from PHP.
    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?

    Quote Originally Posted by mike_bike_kite
    Can you explain where you offer more functionality?
    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.

  12. #12
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by mike_bike_kite
    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.
    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.
    Attached Thumbnails Attached Thumbnails newtable.jpg  

  13. #13
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by compsci
    I am drawing up your structure, seeing how it works and trying to incorporate it in mine
    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.

    Quote Originally Posted by compsci
    You mean using the session_id as part of the URL?
    I mean not passing any security stuff as part of the url and using session ids to hold this type of data.

    Quote Originally Posted by compsci
    Currently my URL is in the format: de4fb9659cadfd97232779a2b4b6f1909c115524138307 - can you guess what it is?
    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.

    Quote Originally Posted by compsci
    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
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •