On Tue, Mar 4, 2014 at 2:15 PM, Kevin Grittner <kgrittn@xxxxxxxxx> wrote: > Roy Anderson <roy.anderson@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
