Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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!

share|improve this question

2 Answers

up vote 0 down vote

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.

share|improve this answer
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 at 10:44
Ick, that's quite a bit worse because the table structure is not self-documenting. – Chris Travers Mar 12 at 13:35
Can you explain what you mean by that, thanks! – Tristan Mar 12 at 14:49
2  
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 – Chris Travers Mar 12 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. – Chris Travers Mar 12 at 14:57
up vote 0 down vote

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

share|improve this answer
The problem is that you would have lots of unused/empty shortAnswer and FullAnswer which is not very optimized. – Tristan Apr 26 at 17:13
What do you mean by 'not very optimized' ? – druzin Apr 26 at 19:36
It means it is not normalized : stackoverflow.com/questions/4286698/… – Tristan Apr 29 at 9:13
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 at 10:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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