Re: Too add a new table or not

Pavs <pavle_sturma_at_hotmail.com> wrote:
: I was wondering what the rule is when it comes to adding a new table
: to a data structure as oppossed to sticking with existing tables and

No rule, sorry.

: shoving all the new attributes in the existing tables. (Assume that
: the data remains normalised)

That's a pretty big assumption.

: My boss with way more experience then myself argues that more can be
: done with less. "Why subject the database engine to more hits across
: multiple tables when you can stick to the one table" he says.

The database engine is my slave -- it should worker harder so I don't have to.

: Well whats the rule?

For query performance? The rule is to minimize disc block reads and seeks. If you have a very wide table, then the DB will be reading in lots of blocks to find the few columns you need. But if you have multiple thin tables, the DB could chew up some time seeking around the disc. So you see how closely modeling application behavior can be necessary. A good compromise is offered by several DB vendors: you can arrange for sets of tables to be laid on disc in matched patterns to reduce seek time. A better compromise is to buy enough RAM to cache everything in memory. A something that you should have already done, is see if your filesystem block size and DB block size are appropriate for your data.

For update performance? Consider the previous points and add the timings for disc writes to the mess. If you have an update-heavy database, and can divide your tables cleanly (and this whole thing isn't some wanking exercise -- you *do* have over 100 columns in this table, right?), then a multiple thin data model has some real advantages. I only wish we didn't have to muck around down here on disc layout.

HTH. Received on Tue Nov 06 2001 - 18:42:16 CET