Re: Is denormalizing via views bad (read: slow)?
Date: Mon, 18 Mar 2002 18:33:43 +0000
Message-ID: <mAqI73LHMjl8EwVc_at_shrdlu.com>
In message <27b86948.0203171817.27d460a3_at_posting.google.com>, Jason
Glover <jglover_at_xtra.co.nz> writes
>I am currently reviewing how one of our databases addresses the
>complicated issue of storing and calculating pricing structures
>involving:
>
>- contract prices
>- quantity breaks
>- other various discounting methods
>
>Having just completed a thorough normalization of the current system I
>have the world's most elegant logical ER diagram encorporating the
>world's most flagrant use of sub-classing.
>
>What I wondering - before I go ahead and do it anyway - whether it is
>more wise to:
>
>a) Implement the physical model fairly close to the logical. Create
>humungously verbose stored procedures to churn prices out of this
>fantastical database structure.
>
>b) Implement the physical model fairly close to the logical. Build a
>swath of views to denormalize the data a fair amount, thus allowing
>considerably easier to follow stored procedures.
>
>c) Implement a physical model that slightly denormalized version of my
>sexy logical model, encorporating whatever "hacks" may be required to
>remove the need for views or jugernaut stored procedures but that
>unshamedly prostitute the perfection of my logical DM.
>
>Have I perhaps had too much coffee?
>
>Personally I prefer the legability of (b), but I am wondering if the
>trade-of of all those nested views is ultimately going to hurt
>performance.
Whatever solution you choose will be a compromise. To decide how far to compromise you need to decide the total cost of each of the three options. Each one will have a different value for setup costs and running costs. If your project is going to run for many years then it is worth accepting higher setup costs in return for a lower running cost. Only you can tell how much to compromise.
Always remember that you get 80% of the benefit for 20% of the effort.
-- Bernard Peek bap_at_shrdlu.com In search of cognoscentiReceived on Mon Mar 18 2002 - 19:33:43 CET