Solved

Problem with Database design. As is needs to use explode function etc, too much array processing:

Posted on 2006-07-09
7
225 Views
Last Modified: 2012-08-13
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: red10901blue10901green10901purple10901orange10901  (answers seperated by 10901, will be split into an array with PHP)
Usertwo Answers: green10901pink10901purple10901indigo10901lavender10901
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..
0
Comment
Question by:stormist
  • 3
7 Comments
 
LVL 10

Expert Comment

by:ThaSmartUno
ID: 17070220
Table 1: (User)
UserID (PK, autoincrement)
SessionID

Table 2: (Email)
EmailID (PK, autoincrement)
UserID (FK)
Email

Table 3: (Questions)
QuestionID (PK, autoincrement)
Question

Table 4: (Question_Answers)
AnswerID (PK, autoincrement)
QuestionID (FK)
UserID (FK)

Table 5: (Converstation)
ConversationID (PK, autoincrement)
UserID (FK)
TimeStamp
Response

This is my suggestion
0
 
LVL 10

Expert Comment

by:ThaSmartUno
ID: 17070221
in Question_Answers ... you would also want an 'Answer'
0
 
LVL 10

Author Comment

by:stormist
ID: 17077037
If each conversation has several responses, wouldn't the conversationID have to stay static? Autoincrementing would change the conversation ID for each response, thereby having no means to track which responses are from which conversation?
0
 
LVL 10

Accepted Solution

by:
ThaSmartUno earned 500 total points
ID: 17077539
I guess you could have:

-- Conversation --
ConversationID (PK, auto)
StartUserID (FK)   ( these two would be optional )
StartTime

-- Conversation_Response --
ResponseID (PK, auto)
UserID (FK)
TimeStamp
Response
0
 
LVL 9

Expert Comment

by:cristi_2112
ID: 17114811
Hello,

I don't know why you should use Table 2 (Email). Could a user have several email addresses ?
If not, you could make :

Table 1: (User)
UserID (PK, autoincrement)
SessionID
Email (Indexed eventually)

Instead of your EmailID, you could use UserID...
Good luck.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now