I have 3 database tables and I need to normalize them. The association between tables like this:
Tables:开发者_如何学运维 1. question 2. answers 3. comments
- (question can contain many answers and many comments)
- (answers can contains many comments)
I know I could use questions and answers in one table and then I solve the problem, but I want to use it this way, so what's the best way to associate 'comments' with 'questions' and 'answers'?
I tried to put question id and answer id into the table of comments and use only one of them for each row, is this right? this way I can use them in php script but I don't know if there is another better way because that I asked.
Thank you.
I would set them up as follows:
Posts (both Questions and Answers would be types of "Posts"):
Post_ID
[Fields Common to Posts, e.g. "Post_Content", "Poster_ID", "Post_TimeStamp" etc.]
Questions:
Question_ID
Post_ID
[Question-specific fields, e.g. "Question_Title"]
Answers:
Answer_ID
Post_ID
[Answer-specific fields]
Comments:
Comment_ID
Post_ID
[Comment fields]
The main idea of having a seperate Posts table is to normalize your database, and allow for a logical structure for comments to point to both. Like APC commented, it also gives you a good setup for developing your site/database going into the future (when you might want other types of commentable "Posts."
While the enum method listed below would work, it's not good database design. In general, I would suggest only using ENUMs when you're representing a real world list of items.. not for organizing your database structure.
Use an extra type
field in the comments
table which holds "question"
or "answer"
for a polymorphic association. Queries then just include that field:
SELECT * FROM `comments` WHERE `type` = 'question' AND `foreign_key` = 42
精彩评论