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?


  • Clearly two different values can have the same hash. What's your plan on handling that?
    – A-K
    Dec 7, 2013 at 21:36
  • Hashes are unique because q_id-list_id-user_module_id is a unique key and the hash is built with those 3 values such as q_idXlist_idYuser_module_idZ
    – Tristan
    Dec 9, 2013 at 9:22

2 Answers 2


How about just a single table with a couple of content columns, something like :

 ID | q_id |...|...| shortAnswer varchar | FullAnswer Text |...|...|

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

  • The problem is that you would have lots of unused/empty shortAnswer and FullAnswer which is not very optimized.
    – Tristan
    Apr 26, 2013 at 17:13
  • What do you mean by 'not very optimized' ?
    – druzin
    Apr 26, 2013 at 19:36
  • It means it is not normalized : stackoverflow.com/questions/4286698/…
    – Tristan
    Apr 29, 2013 at 9:13
  • 2
    Although Nullable columns strictly speaking go against one of the conditions of the First Normal Form, they can in fact be more optimized in real-life applications. Consider an address book, where some addresses have apartment numbers. In a strictly normalized structure, you'd need to have a separate table for the apartment number, linking that to the primary key of the address itself, adding an outer join condition to any query fetching address data. In practice, you'd probably just add a nullable column.
    – druzin
    Apr 29, 2013 at 10:06
  • After a year developing with option B I must say it is a mess to keep the consistency between the hash and the answer. So I think this is the right answer.
    – Tristan
    Dec 17, 2013 at 9:10

Personally I would avoid the twin table approach most, but not all, the time. It leads to duplication of information and if you find that a wording of a question is bad in the db, it takes a bit to clean it up. The cleaner solution would be to have the answer table just join user and question and then you only have one update/insert required.

This being said there can be some valid reasons for the twin table approach. The major one is that if the questions are relatively free-form, frequently changing, etc. and you do not need to track answers across a given form of the question, then a twin table approach forms essentially an "answer log." It isn't great for analytics if you are just looking at questions and answers, but it might be in some other fields. I could imagine linguistic research doing well with a twin table approach. For example it makes it easier to look into questions relating to effects of wording on response (rather than substantive answers to substantive questions).

So in general, I would recommend against a twin table approach unless you know you need it. A simple answer table joining both the question table and the user table is sufficient for the most common use cases and it is far more useful in most cases.

  • Thanks but we don't have any duplication of information in both case, only a duplication of the structure in case A. With one table for the text answers (for specific q_ids). And another table with all other answers stored as varchar.
    – Tristan
    Mar 12, 2013 at 10:44
  • Ick, that's quite a bit worse because the table structure is not self-documenting. Mar 12, 2013 at 13:35
  • Can you explain what you mean by that, thanks!
    – Tristan
    Mar 12, 2013 at 14:49
  • 3
    Ok, if you have a generic table structure with different meanings it is very hard to ramp up on the db structure. Once I worked with a dbase database with tables like customer1 and customer2, with identical structures, that was intended to be joined the way you expected (and each had 256 fields!). The problem of course is that this isn't obvious, and so it took a lot of trial and error to figure out what any given piece of data meant. In software programming and design, clarity is golden, and generic structures joined in odd ways gets in the way of that. tbc Mar 12, 2013 at 14:57
  • If you rename one field from content to question, and the other from content to answer, and name the tables appropriately that isn't a problem though. Mar 12, 2013 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.