Take the 2-minute tour ×
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.

Sorry for poor title, I'll try to explain it in more detail here.

I have an application that often gets a row from table A, then looks up a column from that in a foreign key mapping table (table B), and finally looks up several rows with the corresponding foreign key in table C.

My question is: would it be better for table A to have a column containing data in the particular column we often want from table C? This would be anti-normalization, but would save on a lot of queries.

As an example, let's say we have a table "french words" (table A), with columns like:

id, word, pronunciation, frequency

We also have a table (table B) that maps the "french words" id to any number of "german words" id (table C)

And then in "german words" (table C) we also have id, word, pronunciation, frequency.

When we look up a French word, we often want to get the highest frequency German word that it's associated with via table B. Would it be better to just include a highest_frequency_german_word column in the French words table?

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

Is there some particular reason you're worried about how much work your computer has to do to join three tables?

The purpose of normalizing your schema is to make it much harder to corrupt your data when you insert, update and delete it. Normalizing your schema means that reading your data will be more work (generally), but your code for maintaining your data will be much simpler and therefore much less prone to data corruption errors, as well as being cheaper to build and maintain, of course.

If your data is pretty static, i.e. you write it once and then read it a lot, then the benefits of normalization are less important.

If your performance or resource utilization is very critical, and you can't get the performance you need from normalized data, then you can consider denormalizing your schema. However, whenever you use denormalization you should do it with full knowledge of all of the potential traps you've set for yourself with respect to data corruption.

Normalize by default and denormalize as necessary, and with caution.

share|improve this answer
add comment

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.