hi ,

while designing a database with huge tables that are meant to hold
hundreds of thousdands of rows,

is it a good idea to flatten the tables a bit in order to accomodate
better for queries that require many joins ?

i know that this contradicts with normal forms and puts database
integrity on stake but if it is implemented what

could be the possible benefits and down sides ?

thanks

"steven acer" <dudesterr (AT) gmail (DOT) com> wrote

Hundred of thousands of rows is not sufficiently large, in and of itself, to
motivate departure from normalisation for performance reasons. How
performance gets affected depends of which DBMS and which version you are
using, what your hardware platform is, and a host of other variables.

I have often departed from normalisation, and designed databases with data
joined together in ways that contradict one of the normal forms. This was
almost always in the context of a reporting database, or a database to
support OLAP style activities. The price you pay for making this choice is
that the processes that keep the data up to date are harder to write and run
slower.

My one strong recommendation is that, is you do go in such a direction, that
you follow some other design discipline, one that will help you make
considered judgements about how the denormalized data should be composed.
Avoid just joining data because "it seemed like a good idea at the time."

The design discipline I have followed for reporting and OLAP databases is
star schema. You can learn about it under "data mart" or "data
warehousing". Don't be intimidated by the term "data warehousing". Star
schema is a design discipline that can be scaled down to the size problem
you are working on. It's not hard to learn, but it's hard to accept if
you've already learned how to design normalized data.

I can't say that you really need to denormalize. But if you do, I heartily
recommend star schema design.

On Jul 13, 3:14 am, steven acer <dudest... (AT) gmail (DOT) com> wrote:
It is called denormalizing.

It looks like you know the benefits and problems:

** Plus side is performance and simpler queries

** minus side is data redundancy and potential integrity problems that
go with it

It is important to note that the performance gain expected from
denormalizing is not guaranteed. Why? Because of the data redundancy,
the DBMS must now read more blocks of data from disc, each block
containing fewer rows. So increased disc I/O might kill the expected
gain. The Only way to be sure of the gain is to test it with
PRODUCTION data.

A side comment: a table with hundreds of thousands of rows is large,
but not huge nowadays. Multimillion and billion row tables are huge.
There are folks dealing with multiterabyte sized databases (not me).

One more thing to consider: will the denormalized table be used more
in a transactional processing context or more a reporting context? In
a reporting context (few updates, usually in batches) denormalized
tables are more common.


HTH,
Ed