[thelist] Database Design Question

[Luther, Ron]

Joshua Olson noted:

>> * Then client wants reporting; inventory, backlog, product line
stats,

>Absolutely!  None of those requirements would necessitate
denormalization, though...

Well, I dunno ... I might disagree a bit there: (a) some of those OLTP
structures are amazingly complex (I'm thinking of the Oracle Enterprise
sales order entry module tables for instance - very very ugly to try to
work with), and (b) 'efficiency' (from an OLAP perspective anyway) is a
combination of data structure _and_ the analyst's ability to work with
the data.

So, if I can improve the analyst's ability to quickly conduct ad-hoc
OLAP
investigations by duplicating a few fields to 'simplify' the structure,
then I've gained efficiency and consistency (the ability of other folks
to
replicate the results) by denormalizing the data.


>So long as information is not represented in multiple ways, there's no
>real reason why a database designed for OLTP could not have attributes
>(indexes, views, etc) to facilitate OLAP. 

This is why I think it would be fun to collaborate on this ... your
familiarity with OLTP might help me see more opportunities ... my
familiarity with OLAP might help you see more challenges ... sounds
like fun!  ;-)


>Never heard that, but way cool, nonetheless.  A violation of corporate
policy,
>no matter how nit-picky it may be, is just that.  Even a DBA needs to
appreciate
>the nuances of corporate politics.

IIRC, in the 2 or 3 cases I've heard of - it was more than corporate
politics;
the added indicies slowed the order entry process (high volume centers
running
at very high percentages of capacity) to the point where sales were lost
...
and lost sales can get anyone shown the door.


>Perhaps a bit, yes.  There are probably many people on this list who
would be
>ideal persons to tackle an article such as this.  My relationship with
OLAP vs.
>OLTP is skewed heavily towards OLTP because of my heavy OOP background
.  A
>person with a balanced exposure would be invaluable in writing such an
article. 

I have pretty limited exposure to OLTP. Most of my experience is on the
OLAP
side, report development and ad-hoc analysis of that incoming OLTP data.
Maybe
we'd balance each other out a bit.

I'll try starting a draft outline and send it to you offlist in the next
day
or so. If you get time, you can mark it up and send it back.


>Where's Rudy when ya need 'im?

Oh, I'm pretty sure we can get Rudy to review a draft and offer some
constructive criticism!  ;-)   (I've had a few phone and email chats
with him in the past on this kind of thing.)


RonL.