Are de-normalised tables preferred in any aspect of a project?

Question

While going through interview , My Interviewer asked me a question about Denormalization of tables and its usage in applications.

On guess, I answered, Yes it might be. Being denormalized , you have all columns in a single table where you do not require any kind of joins so your biggest plus point will be performance if proper look up conditions are given. And you can use a denormalized table in Reportings.

Is it correct, what I answered?

This is not a very useful question as stated because it seems to be asking for opinions, about projects, and it's about an interview you had - a good answer that is useful to you would have to know something about the interviewer and what they were trying to learn about you. This doesn't make for a very useful post for anyone else on this site, unless they are also interviewing for the same position with the same person. — Aaron Bertrand, Oct 11 '13 at 16:21
Typically a 3NF DB doesn't slow down lookups much but sometimes it can be reasonable to denormalize. A phrase I've heard, and like to repeat, is "Normalize until it really hurts, then de-normalize until it works" — Matthew, Oct 11 '13 at 16:22
I'm not going to iron fist this one but when it gets closed I suggest you re-word it to ask about pros and cons of denormalization without mentioning interviews / projects or whether your answer was "correct." Or just read about this information. All 4 links were in the top 10 Google results for "denormalization in SQL Server" BTW. — Aaron Bertrand, Oct 11 '13 at 16:25
@AaronBertrand , thats what I was looking for. I had this article once, but was not getting where I had read that. Thanks for reminding. — Amit Ranjan, Oct 11 '13 at 16:33
There is a good question buried in there. The problem is that right now it's stated as asking about the topic as a whole, about which I'm sure entire books have been written (AKA it really "does depend"), so it's not a good fit for this format. If the question was about a much more specific situation, we could certainly tackle that. — Jon Seigel, Oct 11 '13 at 16:49

score 6 · Answer 1 · edited Oct 11 '13 at 17:15

6

Actually the performance can be slower when you have to update the data, which is why denormalization is not normally used as default in OLTP systems.

OLAP - reporting - uses them as well as precalculated aggregates because updates do often not happen in real time but batched and query performance can be paramount.

But for anything transactional - denormalization is not advisable as default approach in application development.

By the way, unless you do reporting denormalization can make your data access slower because while you do not have a join, the table may be a LOT bigger, thus requiring more RAM or more IO budget to answer the same query.

edited Oct 11 '13 at 17:15

ypercubeᵀᴹ

97,895
13
214
305

answered Oct 11 '13 at 16:23

TomTom

4,648
1
16
19

6

Looks like your z and y keys have got muddled! – Martin Smith Oct 11 '13 at 16:27
No totlly. switching german and polsh keyboards at the moment every couple of minutes. – TomTom Oct 11 '13 at 16:32
Why all the downvotes? This is a good summary of some of the reasons to use or avoid denormalization. – Jon of All Trades Oct 11 '13 at 17:22
2

@JonofAllTrades Yeah that is weird. There's nothing wrong with this answer other than the y-z key-switch. Which is obviously a typo problem and easily fixed. – RBarryYoung Oct 11 '13 at 17:25

Are de-normalised tables preferred in any aspect of a project?

1 Answers1