On 03/04/2014 01:40 PM, Merlin Moncure wrote:
On Tue, Mar 4, 2014 at 2:15 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
Roy Anderson <roy.anderson@gmail.com> wrote:
We have an OLTP database and no data warehouse. We are currently
planning out a build for a data warehouse however (possibly using
Hadoop). "X" is recommending that we convert our current,
normalized OLTP database into a flattened Star Schema.
I'm not going to repeat good advice you've already gotten in other
answers, but I will point out that complex reporting off of
normalized data is often much faster if you have been able to use
natural keys, even if you need to go to multi-column primary keys
to do so. One of the biggest down-sides of synthetic primary keys
(where, for example, you might have a single-column PK column
called "id" in every table) is that forces one particular route to
"navigate" the tables. With natural keys a complex query often
finds intriguing plans to give the results you ask for using plans
you might never have thought of, and which can be orders of
magnitude faster than the plans which would be possible if the
joins are all done using synthetic keys.
If we ever happen to meet, you just bought yourself a steak dinner
with this email. Natural key database design has to my great
displeasure become something of a lost art. Data modeling and
performance expectations have really suffered as a consequence of that
knowledge gap. Now, natural keys have issues also -- update
performance on the key in particular -- so you have to be nimble and
adjust the model as appropriate to the task at hand.
merlin
Do you make a distinction between a key and an index? I'm not picking up on design-by-natural-key and what that entails. Especially the notion that the natural key of a given item might be mutable. What stops it from colliding with the next item? (I have not had the pleasure of working in a domain where natural keys are obvious if they existed at all. "What's in a name", after all. )
