ineffabelle: (Default)
[personal profile] ineffabelle
my more hyper-tech savvy friends:
is there any good reason NOT to normalize a relational database as much as possible when designing it?

(no subject)

Date: 2010-06-02 12:20 am (UTC)
From: [identity profile] zare-k.livejournal.com
My opinion is that the initial database design should be strictly normalized. However, if you need to optimize for read performance then some later denormalization may be appropriate.

(no subject)

Date: 2010-06-02 12:34 am (UTC)
From: [identity profile] zare-k.livejournal.com
Yes, denormalized views are fine.

(no subject)

Date: 2010-06-03 04:38 am (UTC)
From: [identity profile] thefowle.livejournal.com
depends on your db whether the denormalized view is going to have any better performance than the longhand query.

last uber-fancy sql i wrote, the thing performed awful on oracle until we turned some of the views it was using into materialized views, and man did that baby burn rubber after that.

postgres just got recursive queries, which in a kind of similar fashion can help you avoid having to re-join your normalized data multiple times.

I'm translating from what A is telling me

Date: 2010-06-02 02:07 am (UTC)
From: [identity profile] polyanarch.livejournal.com
A couple of reasons:

One is risk, in case something changes. For example; I have a table which has very sensitive information. I have two keys of who the last person was who updated it. It's the ID of the person, and a surrogate key involving the persons login information (active directory login). The reason for this is, in case anything should be corrupted, there is another key. That's technically denormalized and this is a special case.

Two. If you are going to have another table for something with small storage requirements like a date or a numeric variable rather than linking to a table it may make more sense to just have the value there -because you won't really be saving much space.

There are other good reasons but that's all she's coming up with at the moment.

And no, I have no idea what I just typed... I'm taking her word for it ;)

(no subject)

Date: 2010-06-02 03:36 pm (UTC)
From: [identity profile] fairyhead.livejournal.com
You need to always analyze what's going on with a table. There can be times that strict normalization actually degrades performance, so it makes more sense to denormalize the table.

I'd recommend digging around some on asktom for some in depth explanations: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1027231318121

Profile

ineffabelle: (Default)
ineffabelle

December 2012

S M T W T F S
      1
2345678
9101112131415
16171819202122
23242526272829
3031     

Most Popular Tags

Style Credit

Expand Cut Tags

No cut tags