[compsci]

Hello all,

I want to make a messaging system where emails sent are linked, like the way google/yahoo do theirs. So i send an email and then you reply, i want the email i sent and the one you sent to be linked so that I am able to make them into a thread.

Is this design correct? Any more i can do?

http://img157.imageshack.us/my.php?i...bdesignxa1.png

Thanks all

[r937]

what's a convo?

those tables look too similar to be separate tables

[r937]

what's a convo?

those tables look too similar to be separate tables

[compsci]

Hi r937,

Apologies for my terrible slang! Convo is supposed to be short for conversation.

My thinkng behind this is that I have a table (message) which holds all the first messages and then another table (convo) which holds all the other messages which are linked to the message table by the msgID.

Actually thinking about this again, how will i know the order of the conversation? I can compute it by the date field but is there another way i can do this naturally through a good db design?

Thanks

[r937]

sure, there's another way, just have the reply link to the email it's replying to using its id

and they can both be in the same table

[compsci]

Oh cool! Thanks for that. It will work like that but there is still a fair bit of coding to determine which message is linked to which reply.

I mean if we picked any message (say 1) then i would look in the replyfield to see if it had any replies, if it did then go find that mesage and do the same again. No question this will work, but if the table gets really large then it will be really inefficient right?

We could break it down, but the only way i thought was the above and it was a bit silly.

[r937]

here is a suggestion

read the following article, and where is says "category" i want you to think email, and where it says "subcategory" i want you to think reply

other than these names, it is exactly the same database structure, and you will be using the exact same queries

--> Categories and Subcategories

[compsci]

I am not ignoring you, that was a great article, looks like its exactly what i need. I have also been reading some of the resources after the article, they spoke about alternatives but I didn't understand them!

Writing the SQL seems complicated even though I have an example, but I am learning about LEFT OUTER JOINS now so that hopefully it becomes easier.

[r937]

don't forget that when you try it out, and if you have problems, you can always come back here for help

[compsci]

Table test

| msgID | msg | reply ID |
--------------------------
| 1 | m | 0 |
--------------------------
| 2 | m | 1 |
--------------------------
| 3 | m | 2 |
--------------------------
| 4 | m | 3 |
--------------------------
| 5 | m | 0 |
--------------------------
| 6 | m | 5 |

SELECT tester.msgID as msg_ID,
up1.msgID as up1_msgID,
up2.msgID as up2_msgID,
up3.msgID as up3_msgID

from test as tester

left outer
join test as up1
on up1.msgID = tester.replyID

left outer
join test as up2
on up2.msgID = up1.replyID

left outer
join test as up3
on up3.msgID = up2.replyID
where tester.msgID = 2
order by msg_ID

After reading joins i attempted to customise the sql under the
"The path to the root: the breadcrumb trail" section. It works great.

But I want the above to able to search both up and down so that all messages
retrieved no matter which msg the user clicks on.

I would be very grateful for more help.

[r937]

Originally Posted by compsci

But I want the above to able to search both up and down so that all messages
retrieved no matter which msg the user clicks on.

could you explain how both up and down would be used together in a real situation?

[compsci]

Originally Posted by r937

could you explain how both up and down would be used together in a real situation?

It is most likely that my understanding is flawed.

But for example say you have a messaging system: A B C D E - this is the series of the convo (letters are people and their email) - say that person C clicked on a old notification that an email was sent to them. I need to be able to get the past messages and the messages that followed after that - this is why i need an up and down search to get complete list.

On my previous post, where i drew the table - if I used php to call the msgID 3 and all related messages - i need to get messages 1,2,3,4.

[r937]

SELECT columns FROM (
query going up
UNION ALL
query going down
) AS both

[compsci]

The above looks good to me, but before i even reached the stage of using it, I got stuck in trying to do the statement for going down.

Referring back to your great article - It says I have to specify the number of levels to go down (Under the section: "Displaying all categories and subcategories: site maps and navigation bars"), but it is variable - so how do I make the SQL understand this?

[compsci]

Before I even try the concept I - my sql is crap! Is there a syntax error here?

SELECT msgID, msg, replyID FROM (

 SELECT tester.msgID as msg_ID,
up1.msgID as up1_msgID,
up2.msgID as up2_msgID,
up3.msgID as up3_msgID

from test as tester

left outer
join test as up1
on up1.msgID = tester.replyID

left outer
join test as up2
on up2.msgID = up1.replyID

left outer
join test as up3
on up3.msgID = up2.replyID

UNION ALL

select root.msgID  as root_msgID
     , down1.msgID as down1_msgID
     , down2.msgID as down2_msgID
     , down3.msgID as down3_msgID
  from test as root
left outer
  join test as down1
    on down1.replyID = root.msgID
left outer
  join test as down2
    on down2.replyID = down1.msgID
left outer
  join test as down3
    on down3.replyID = down2.msgID
 where root.replyID is null


) AS both