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.
On Fri, Aug 29, 2008 at 11:11 AM, Jerry Schwartz <jschwartz@stripped
> >-----Original Message-----
> >From: Kevin Hunter [mailto:hunteke@stripped]
> >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
> >> 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
> 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
> 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.
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe: http://lists.mysql.com/mysql?unsub=1
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: