I recently started developing with MySQL and PHP. I'm building a web application which allows users to post a question which other users then post responses to, much like Yahoo! Answers. I would like advice about how best to structure a database to support this application. It would seem natural to store responses to a question in their own table, but this would require creating a new table for every question. Is this done? Is there a better way?
Thanks for reading.
Actually, you can just have a comments table with the following columns (at least):
- questionID
- commenterID
- commenttxt
So when you display the page showing question#42, you will query the comments table where questionID = 42.
My application will support multiple comments for every posted question. Wouldn't using the model you suggest require me to store all a question's comments in a single table cell? It's possible (until the length of the complete comment text exceeds the largest table variable's storage capacity), but it seems unnatural and would require additional overhead.
Would creating a new table for every question posted be unreasonable? Would it be very inefficient?
So, relation is One-To-Many
One Question has many Answers and Answer has many Comments
So...
Question(QID, QSubject, QText,....)
Answer(AID, QID, AText,......)
Comment(CID, AID, CText,.....)
haa?
Ramy Mahrous, I don't understand your proposed solution. Just to ensure you understand what I'm trying to do, please allow me to reiterate the intended design of my application: one question has multiple responses (that's all).
I would also like to take this opportunity to ask again: Would creating a new table to store the responses to every question posted be unreasonable (assuming thousands of new questions posted every week)? Would it be very inefficient?
Thank you for reading.
sorry??!
My application will support multiple comments for every posted question. Wouldn't using the model you suggest require me to store all a question's comments in a single table cell? It's possible (until the length of the complete comment text exceeds the largest table variable's storage capacity), but it seems unnatural and would require additional overhead.
Nope. I meant well, but I didn't explain well... :) *rewind*
Answers table structure:
- answerID
- questionID
- commenterID
- commenttxt
The answerID is unique, it will reference a questionID that can be repeated in the answers table... Here is a sample answers table:
ansID: 1
quesID: 1
commenterID: 34
comment: "No, you can't eat dogs in the US. That's illegal."
ansID: 2
quesID: 1
commenterID: 8
comment: "Why would you want to eat your pet?! That's disgusting!"
ansID: 3
quesID: 1
commenterID: 72
comment: "Someone delete this question!!!!!"
ansID: 1
quesID: 2
commenterID: 28
comment: "Hi, confusedgeek489! I had the same question myself. You can go to (link)daniweb if you want to find answers. Cheers!"
---------------------------------------------------------
I forgot why using the multiple tables approach is not advisable. (I tried to do it before... :)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.