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] > >
