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 agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.