A few questions about Normalization!
-
I'm learning about data modeling right now and was wondering,
could any of you share with me... what is your real-world approach to normalization and de-normalization ?
How do you know when you need to normalize and when is it not worth the effort ?
And, can anyone give me an example of when they might need to denormalize ???
thanks in advance for any real-world insight you can offer.
-
You should always normalize.
You should denormalize only when you have no other choice (for performance) and if the data is for reports only (less overhead of keeping 2-3 tables in sync all the time). One exemple I have here is that at each month end we have to check how much ahead or behind we are in our contracts (have we billed all the work done, does this cover our expanses...). This data can be fetched only the 1st of the month or else the data will have changed and won't be as meaningfull. That data is extracted and inserted in a reporting table (denormalized) and is used only for further analysis.
The other exemple would be a large compagny wanting to know a lot of information and that the query to run the report takes a few hours. Then that query could be ran a only few times a day into a reporting table.
-
Thanks!
Yes, I know you should always normalize.
I guess my question was.... how far is realistic to go..
3NF seems doable, but sometimes it might not be feasible ?
-
I don't have enough design experience to answer this one. I guess some things can be hard to do, but I don't see them being impossible.
-
FWIW I worked on a project some years ago where the decision was taken (not by me! ) to not normalize certain tables for performance reasons. Boy did we end up in a world of hurt! The further we tried to go, the harder the code was to write.
Ever since then I have always, always insisted on TNF.
-
In my opinion, it all depends on your application. If you have a transactional system with lots of capturing and inserting, then 3NF at least.
On the other hand, when you want to optimize for reporting you want to denormalize or design according to what we in the BI industry call a dimensional model. A dimension model will always be best for performance in a reporting system.
The catch 22 you might be sitting with is a transactional system from which you want to do reporting as well. Here it comes down to proper planning and also case by case analysis. In some instances you might want to normalize, and in others denormalize.
Storage space is very cheap today, so denormalizing won't hurt too much. Having a 3NF system which is used for reporting primarily would mean lots of indexes taking up more space than a denormalized db would have. This would also not be ideal.
Unfortunately there isn't only one correct answer for every situation/scenario. That's why design is so important.
Hope this helps.
-
You would usually go to thrid or fourth normal form for design and then denormalise for performance and implementation.
A quick rule of thumb on existing columns is that if it allows null or contains duplicates it should be normalised - of course this is an extreme viewpoint < grin >
As Martin says there is no real answer, it's a case of application, design and what works best for you.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Always at least 3NF, especially if you are designing a transaction heavy db. I think the performance issue is sometimes a straw man in the normalization discussion, and the issue can largely be alleviated with good indexing and well thought out queries. Keep your OLTP database normalized and create a data warehouse, or at least separate staged denormalized data for reporting performance.
I've found that a lot of pressure to denormalize comes from developers who don't want to have to deal with joins, and don't think in relational terms. Fortunately, the developers in my current shop are 3NF freaks.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply