| Normalization & De-Normalization [message #90477] |
Tue, 11 November 2003 07:32  |
Rajarshi Dasgupta
Messages: 52
Registered: October 2001
|
Member |
|
|
What are the factors on the basis of which we should design a Normalized Table structure and a de-normalized one? What can be pros & cons in both the cases W.R.T. a D/W application, a ERP application and an OLTP application?
Say I have 2 schemas for doing same job. But one contains all normalized tables and the other all de-normalized ones. How to compare the design of two schemas to decide which one to keep and which one not to? (Example say comparing performace, space utilization etc)
|
|
|
|
| Re: Normalization & De-Normalization [message #90478 is a reply to message #90477] |
Wed, 12 November 2003 08:55  |
Epe
Messages: 99
Registered: March 2002
|
Member |
|
|
Hi,
you have to decide yourself which parts to normalize and which to de-normalize, because there are no rules of thumb possible...
Anyway...
Normalizing : means that there is less storage, so less disk space is needed. It also means that you have to update or insert less (because the data is only in one place). So this is good for systems that have to do a lot of updates or inserts like OLTP systems.
Denormalizing : means that you require more disk space and also more insert or update time. It does improve selects though, because less joins are needed to retrieve the data. On a Datawarehouse, there is a lot more selecting done than on an OLTP, and a lot less inserting or updating done, or at least focused on a shorter time (preferably on periods when no users are accessing the data, like at night). That's why a lot de-normalisation is done in a Datawarehouse situation.
How much should be de-normalised, really depends on what queries are run (or over how many tables a query should join if not de-normalized).
Note that also in an OLTP situation, some de-normalisation can be usefull, again depending on how many inserts and updates there are, compared to the queries (how many and how complicated) for the table(s) in question... and that a Datawarehouse should not completely be de-normalized (only where usefull to give faster queries).
Beside all that, it also depends on your hardware (how much disk space available, how many cpu's and how fast they are etc.) A lot of disk space means you can afford to store the same data multiple times, so then de-normalisation can be done. Also the kind of disks can be relevant : some raid configurations are very slow to write to.
Basically I would say : don't de-normalize, unless it will speed up your queries noticably, and if you can affort it (space-issue).
I hope I could help a little bit...
Cheers,
Epe
|
|
|
|
| Re: Normalization & De-Normalization [message #90494 is a reply to message #90477] |
Sun, 07 December 2003 15:46 |
Martyn Roberts
Messages: 9
Registered: July 2003
|
Junior Member |
|
|
Hi,
I always start out with a normalized design, regardless of db size, and only denormalize when I can not find another way to boost performance.
I worked on a huge system once and had to make only 2 denormalizations. Funnily enough one of those resulted in a bug in production.
Have you considered Oracle's Materialized Views. This gives you the best of both worlds. A normalized underlying structure, with a denormalized interface for accessing data.
regards,
Martyn
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
