Yours is the classic case of the distinction between OLTP and OLAP, and in
my opinion you are doing it exactly right. For analysis purposes,
de-normalization is a good thing because you eliminate the joins and
increase performance, but even more important, you don't slow down the
data-entry process while analyzing the data. Personally, I like the OLAP
data to live in another database, but your approach of rebuilding the
de-normalized table nightly works equally well.

Arthur


On Fri, Aug 29, 2008 at 11:11 AM, Jerry Schwartz <[EMAIL PROTECTED]
> wrote:

> >-----Original Message-----
> >From: Kevin Hunter [mailto:[EMAIL PROTECTED]
> >Sent: Thursday, August 28, 2008 10:59 PM
> >To: John Smith
> >Cc: MySQL General List
> >Subject: Re: Normalization vs. Performance
> >
> >At 2:11pm -0400 on Tue, 26 Aug 2008, John Smith wrote:
> >> So how bad is this? The mentioned query will be the query which is
> >used
> >> the most in my application (yes, it is going to be a forum).
> >> Should I break normalization and save the date of the root in each
> >node row?
> >
> >My recommendation is no.  Normalization is a Good Thing, and you'll be
> >hard-pressed to convince me otherwise.  In the long run, you (or
> >following programmers) will thank yourself if you maintain a normalized
> >core data model.  Seriously.
> >
> >But speed and performance are the here-and-now issues while DB
> >technology "catches up" to demands.  Have you looked at temporary tables
> >or materialized views?  These might help you in this endeavor.
> >
> [JS] You can sometimes cheat.
>
> Our database is normalized, but many of our users want to use MS Access to
> get at the data in read-only mode to extract data into Excel. Rather than
> trying to teach them how to define the necessary JOINs, I periodically
> build
> a non-normalized table for them. For example, I take the values from a
> dependent (1:n) table and use GROUP_CONCAT to stuff them into a single
> field
> in the unnormalized table.
>
> True, the data isn't up-to-the-minute. I truncate the table and reload it
> daily. It is good enough for them.
> >Kevin
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]
> >infoshop.com
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>

Reply via email to