List:General Discussion« Previous MessageNext Message »
From:Arthur Fuller Date:August 29 2008 2:37pm
Subject:Re: Normalization vs. Performance
View as plain text  
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 <jschwartz@stripped
> wrote:

> >-----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
> >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/mysql?unsub=1
> >infoshop.com
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>

Thread
Normalization vs. PerformanceJohn Smith26 Aug
  • Re: Normalization vs. PerformanceKevin Hunter29 Aug
    • RE: Normalization vs. PerformanceJerry Schwartz29 Aug
      • Re: Normalization vs. PerformanceArthur Fuller29 Aug
  • Re: Normalization vs. PerformanceMartijn Tonies29 Aug