de-normalized table VS view of multiple normalizes tables

cliff boley

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

John McKown

Re: de-normalized table VS view of multiple normalizes tables
(in response to cliff boley)
Personal opinion time from someone who is not really a DBA (me).

To the extent possible, I like tables that I design (at home, for my own
amusement) to be as normalized as possible. In your case, my only real
question would be: "Will de-normalizing the table result in significant
performance improvement?" And I don't mean 1 or 2 percent. I mean at
least 5 percent. If not, then I'd go with the VIEW. That will stop the
whining from the developer while retaining a "purer" set up.



--
John McKown
Senior Systems Programmer
HealthMarkets
Keeping the Promise of Affordable Coverage
Administrative Services Group
Information Technology

This message (including any attachments) contains confidential
information intended for a specific individual and purpose, and its
content is protected by law. If you are not the intended recipient, you
should delete this message and are hereby notified that any disclosure,
copying, or distribution of this transmission, or taking any action
based on it, is strictly prohibited.


-----Original Message-----
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

Philip Gunning

de-normalized table VS view of multiple normalizes tables
(in response to John McKown)
Cliff, when others outside the database realm start trying to design
tables for you, it is a bad sign..Like Craig Mullins, I'm not a fan of
views, but better solution than going de-normalized when not absolutely
necessary (and most times it is not necessary). Had a consulting firm when
I was a lead DBA at a company, try and design our tables for us based on
windows, drop down lists, radio buttons they needed to populate...so they
wanted database design by drop down list, radio buttons instead of logical
design for performance, scalabilty, data integrity, etc..Of course they
were join and set theory averse :), Anyway,because of politics, went
with "other firms" design and when it went into production, nothing
worked :), as predicted. Of course, now as a consultant, I wouldn't do
that do anyone! Another quick story, I once did some work at a place where
they said, we don't have any problem SQL, so you won't find any. I raised
my eyebrows when I heard that but thought maybe that was the case. When I
started looking for suboptimal SQL ( I believe it was using Detector at
the time), I couldn't find any joins going on. Turned out, THEY WERE NOT
DOING ANY AND WERE DOING ALL APPLICATION JOINS. Converted one big query to
use a join or two and query went from hours to seconds. Now in an extreme
case, I'm sure someone could write an app to outperform DB2 but it would
be the exception nowadays. Have a nice day... May the force be with
you. :) Phil
-----Original Message-----
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

Mike Bell

Re: de-normalized table VS view of multiple normalizes tables
(in response to Philip Gunning)
A long standing issue - because everyone has an SQL statement that would run
faster if the tables looked like 'just so'. The full answer is dependent on
lots of things - such as

1. Is it a transactional system or a data warehouse?
Transactional systems almost always do better with normalized tables -
data warehouse you don't need the normalized table to validate the data
since that (should/might/would) have occurred in the transaction system.
2. What would the maintenance be required for a denormalized table - Who
would write it? How do you validate it is correct? Nothing to make a project
go sideways like a denormalized table that isn't quite correct.
3. My favorite answer - send them the link to DB2 estimator and have them
build a performance model for both designs (for both the update and
reporting process). If they do that and the numbers are correct, then
recruit them as a DBA.

Mike
HLS Technologies

-----Original Message-----
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


---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003

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

Dee Reins

Re: de-normalized table VS view of multiple normalizes tables
(in response to Mike Bell)
We have used Views extensively for Web Focus applications in DB2 and
Oracle. It simplifies the Web focus, and there are fewer places to
change code. Views are Especially handy if end users are creating
their own reports. Views make the database look like an excel spread
sheet, which end users understand.



If this is not perspicuous please let me know.
Dee Reins
360 905-7343 Business
360 905-7212 Fax
360 608-5262 Cell

_____

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

Troy Coleman

Re: de-normalized table VS view of multiple normalizes tables
(in response to Dee Reins)
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