[AccessD] On DB Bloat, Bad DB Design, and various

Hi Arthur:

Sorry in advanced for being long winded but some answers take a while to
explain.

Normalizing a database actual decreases it's performance while improving
it's data consistency by removing redundancy. Beyond a certain point the
performance hit becomes too much, there is no longer significant improvement
to data integrity and that is where a qualified Database designer can
carefully de-normalize the database. It is a careful balancing act of
performance versus consistency.

I understand there are a series mathematical algorisms that can be used
calculate the theoritical relationship between these two apparently opposing
forces. I do not have immediate access to this set of formulas but have sent
an email to a systems-engineer friend of mine and I can pass the information
along, if anyone is interested. Some of the members the list probably have
access to the 'math' as well. I have a book called 'Structured Systems
Analysis' written by Chris Gane and Trish Saron, that I used in a
night-school course, up at the University, a 'number' of years ago and it
provided a very good over-view of data, database design and the issues
around data management.

Of the five basic levels of Normalization I have never gone beyond three, in
a real application. A number of years ago a systems company won the contract
and moved the provinces medical data into an Oracle DB. The resultant system
was purist dream. Unfortunately, when the data was ported the system ground
to a halt. On-line transactions would take up to fifteen minutes and I heard
some reports would take up to four days. My understanding is that the design
was normalized down to level 5. The whole design had to be rebuilt and it
was; de-normalized back to level 3.

Here is a data example that is not normalized but became the method of
choice because of it's performance.

Given: A table named Staff. Some of the staff are management, supervisors or
a regular staff member. What design would give the fastest results?
1. Create two tables one for staff members and another joined table for
staff-ranking. When extracting the staff and relationships build a SQL
statement that joins the two tables and process same.
2. Add all personnel into the same table and add a field and key for
staff-ranking. When extracting the staff from the table build a SQL
statement that makes two copies of the table, joins one to the other and
then process same.
3. Add an extra field to the staff table that hold the staff ranking number.
When extracting the staff from table the SQL uses one table.

Even though the method used in number 3 does not result in a normalized
table the performance gain is substantial and apparently increases
exponentially, relational to any other design structures, when large numbers
of staff were added...say Ministry wide.

There is a lot of math and case studies that support a balanced position
when doing database design.

I hope this gives you something to think about.
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Arthur Fuller
Sent: Monday, May 24, 2004 7:51 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various


I have never yet seen a case where denormalization is correct, except in
OLAP apps, which are fundamentally different that OLTP apps. I have been
overruled by higher-ups on this point several times, and never once been
convinced of their correctness. Show me a case in an OLTP app where
denormalization is correct, and demonstrate why it is correct as opposed
to the "logically correct" model.

Arthur

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
(AccessD)
Sent: Monday, May 24, 2004 3:24 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various

When a database has been completely normalized then to improve the
performance, the DB has to be 'carefully' de-normalized. This is where
the art comes in, like tuning a guitar.

There are many photographers out there who claim to be professional. It
is not enough to simply know all the rules of composition, design and
colour. There is an indefinable property that makes a photographer a
master of their craft. Their ability to interpret what they see and
produce can is not fully qualified; if it could be we would all be
Masters.

The same is true with programming and database design.

IMHO
Jim


--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com