de-normalized table VS view of multiple normalizes tables

cliff boley

de-normalized table VS view of multiple normalizes tables
Thanks to everyone who responded to this question. Your comments were
exactly what I was looking for.
Of course now I'll have to do some thinking and make some decisions,
darn! :-)

With the help from the "list" I may be able to keep fooling my employers
that I'm a real DBA.

cliff:-)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Troy Coleman
Sent: Sunday, December 31, 2006 2:36 PM
To: [login to unmask email]
Subject: Re: [DB2-L] de-normalized table VS view of multiple
normalizes tables



Hi Cliff,

In most cases your normalized design is going to be the best
design for long term flexibility supporting design changes to your
system. For performance reasons you may find it necessary to
de-normalize. For example, you may decide to put an indicator on a
table that tells your application program that you have some child rows
on another table. I've seen this when 80% of the time you have no data
so why go look for it 100% of the time. Another example is putting 12
monthly total columns on a row instead of having 12 rows with a month
column and amount column.



I think the real problem you have is the skill level of your
developers and the understanding of SQL. You should bring someone like
Sheryl Larsen into your organization to customize the SQL training using
your system as part of the training. I've seen this at several companies
and it really helps.



You always have pro's and con's to any argument. Using views I
think has more con's then pro's. If you have a complex SQL statement
that is used in a lot of different programs then you should either look
into making the statement part of a common I/O routine (my preference)
or make it a view. The benefit of seeing the RAW SQL in a program is
you can see what the statement is doing and it is easier to debug and
tune. The main reason you put SQL in a view is to simplify the code for
less experiences people who need to use report writing tools to get the
data, or the report writing tool does a poor job generating the correct
join syntax so you write the statement and store it in a view to be used
by the tool.



The downside of views is that DBA's have to maintain them. If
the SQL is in the code then the programmer can make the changes needed
not the DBA. Personally since I'm a DBA I don't really want to have to
maintain the views so I like most others avoid them when at all
possible.



Good Luck,



Troy Coleman




________________________________


From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of BOLEY Maurice C * Cliff
Sent: Friday, December 29, 2006 3:47 PM
To: [login to unmask email]
Subject: [DB2-L] de-normalized table VS view of multiple
normalizes tables



Hi all,

I had a developer complain about needing to use multiple joins
of normalized tables just to find the row he wants
in just one table. He prefers de-normalized tables which make
his queries easier.

I thought leave the tables normalized (to keep Cobb's spirit
from haunting me) and create a view for the user.

I'm interested in some comments, good idea, bad idea, good for
performance, doesn't matter, etc.

I will defer to the great wisdom of the oracle (not the dbms)
DB2 Database Discussion list at IDUG.

thanks and have a happy new year!

cliff:-)


------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Cuneyt Goksu

Re: de-normalized table VS view of multiple normalizes tables
(in response to cliff boley)
de-normalized table VS view of multiple normalizes tablesHi,

My personal opinions;

1) Try to stay at normalized. Easier for Data Modelling, Integrity, etc...
2) create indexes for your join operation columns, FKs... (Performance,
Performance)

I have seen 5-6 ways joins, with millions of rows running in less than 10
seconds with proper indexes and correct data distribution statistics.

Iyi calismalar, Best Regards, Saludos, Lijepi pozdrav

-------------------------
Cuneyt Goksu
DB2 Specialist
UDB for z/OS V8 Certified
+90 555 2092562
+90 532 2540342
-------------------------
Think Enterprise, Think Z
-----Original Message-----
From: BOLEY Maurice C * Cliff [mailto:[login to unmask email]
Sent: Friday, December 29, 2006 11:47 PM
Subject: de-normalized table VS view of multiple normalizes tables


Hi all,

I had a developer complain about needing to use multiple joins of
normalized tables just to find the row he wants
in just one table. He prefers de-normalized tables which make his queries
easier.

I thought leave the tables normalized (to keep Cobb's spirit from haunting
me) and create a view for the user.

I'm interested in some comments, good idea, bad idea, good for
performance, doesn't matter, etc.

I will defer to the great wisdom of the oracle (not the dbms) DB2 Database
Discussion list at IDUG.

thanks and have a happy new year!

cliff:-)

--------------------------------------------------------------------------
------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm