I need some design help for my database to manage a special kind of messaging system. its structured by sessions. Each session has 5 questions. So user one might initiate a session with user 2, with 5 simple questions simplified:
Whats your fav color? why?
where were you born?
etc..
user one sends the questions, with his answer already noted. Then user two puts his answers to each question and it is sent back, which user one can reply if he/she desires. Basically like a message thread of each question, but each one group by 5. I need each thread or session to be visible to under each user's account. I need help on an efficent structure for the database to keep track of all this data.
Can anyone offer any suggestions?
Each question has to be saved individually and asked to the responder. Then a message thread can initiate..
Table Sessions
_______________
I've gotten this far but I need something to organize the responses individually based on the question. The site asks the questions manually and individually rather than just pasting them up there and being an actual message board. Here is what I've come up with, please let me know if you can make it more efficent: (assume all answers are colors)
A Column:
Session ID: Primary key
userone e-mail: example@dot.com
usertwo e-mail: example2@dot.com
Questions asked: 12, 15, 19, 22, 10 (question numbers from another table)
Userone Answers: red10901blue10901green1090
1purple109
01orange10
901 (answers seperated by 10901, will be split into an array with PHP)
Usertwo Answers: green10901pink10901purple1
0901indigo
10901laven
der10901
Conversation timestamps userone: 11/27/05, 12/30/05
Conversation responses userone: Thats interesting, I agree (answers seperated by comma, will be matched up with data)
Conversation timestampes usertwo: 11/28/05, 12/31/06
Conversation responses usertwo: blah blah, blah
Somehow it seems inefficent.. Like all the seperators and array processing that must occur.. Any suggestions to avoid the need for array seperators would help a lot..
