We have one keyword table and different tables connecting to the keyword table as the interface table (e.g question_keyword, answer_keyword). We would like to create one master keyword table so when the user search for a word it can find the word is in question table or answer table and retrieve information from that table. We thought of having one master_table(id, question_id, answer_id, keyword_id). Is this design correct? and if we have more tables (means more columns in master_table), how would be the performance (since it might make the master_table as sparse matrix)?

  • Is this design correct? No. One keyword may reference to many questions/answers. One question/answer may reference to many keywords. So reference type is M:N, and it needs intermediate joining table. For example, it can look like CREATE TABLE references (keyword_id INT, story_id INT, story_type ENUM('question', 'answer'));. – Akina Jan 30 at 11:02
  • @Akina - I would not bother normalizing keywords; the hassle of the extra JOIN exceeds the cost of having the keyword spelled out repeatedly in references – Rick James Jan 30 at 19:10
  • @RickJames In this case, even a simple typo in keyword can cause problems. Which is autodetected/autocorrected hardly, especially when misprint produces incorrect, but legal, word. – Akina Jan 30 at 19:22
  • master_table is the intermediate table which refers to keyword, question and answer tables. So having one intermediate table for question e.g question_keyword and one intermediate table for answer e.g answer_keyword is better than having one master_table? – Zizi Feb 4 at 4:26

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.