I have a table answers that has several columns including the content iself such as
ID | q_id | list_id | user_module_id | content | updated_timestamp
The content column is a varchar since we want to be able to search through the answer content quickly.
However, we also want to be able to store full text as answers. There are two approaches:
A. Using two "twin" table (with the same columns names) : a table with the column content of type varchar and a table with the column content of type text.
B. Using a single answer table and storing in the content column a hash key such as q_id7user_module_id362 and use another table that contains only the text entry:
hashkey | content
This approach seems cleaner, however it means doing two UPDATEs AND INSERTs for each entry modification and a JOIN on the two table to pull out the information.
In general is this bad practice to have "twin" tables in a database?
Thanks!
Best Answer
How about just a single table with a couple of content columns, something like :
You can then place an unclustered index on the shortAnswer, and write a crud procedure which trims the user's answer for the shortAnswer field, while inserting the whole answer in the FullAnswer field