Re: Table design decision

If only one column out of 50 is ever used for a given row, why not change the table to be something like:

col1,
col2,
...
col_type where col_type has a value of 1,2,3,..49,50 (or something like that to indicate
colx

with col_type and colx used in the FK.

-- 
Terry Dykstra
Canadian Forest Oil Ltd.


"DA Morgan" <damorgan_at_psoug.org> wrote in message
news:1137000012.542324_at_jetspin.drizzle.com...


> Tarby777 wrote:

> > Hi all,

> >

> > I've inherited a monster table, and have to make a decision about

> > how/whether to normalize it.

> >

> > It has 50 FK columns, each one with a FK constraint and a supporting

> > index. The worst thing from a design perspective is that in each row,

> > only exactly one of those FK columns will ever be populated in any

> > given row. All the columns get populated, but never in the same row. So

> > as things stand, every row contains 49 nulls, and there are 49 index

> > writes that are of little use because the application will never

> > "select * from this_table where FK column is null".

> >

> > BTW, each one of the FKs is set up for cascading deletes, so a single

> > table with a single FK column isn't an option. Currently, a row in the

> > existing table is deleted automatically when a row in any of the 50

> > tables referenced by the FKs is deleted.

> >

> > I'm thinking about replacing the existing table with 50 new tables,

> > each having one FK column with a supporting index, but I'm unsure of

> > what how it might affect performance. I plan to implement this

> > structure in a new DB to test it, but I'd be interested to hear from

> > you all about whether you think the way in which I plan to revamp this

> > table will help or hinder performance. I'm guessing that writes will be

> > quicker, mainly because there will be 49 fewer index keys to write, but

> > I'm not so sure about reads. At the moment, all the data is held in one

> > table, so I'm guessing that there's at least *some* chance that during

> > a SELECT, a single block read might find > 1 of the required rows. In

> > the new model, those two rows could be in different tables and that

> > would mean two physical reads, two blocks read into the buffer pool and

> > a possible negative impact on the buffer pool hit rate. Also, I guess I

> > might need to increase the dictionary cache size, or suffer the pain of

> > more recursive SQL.

> >

> > I appreciate it might not be possible to give detailed recommendations

> > without knowing how many rows are involved / how often the table is

> > read/written etc, but any advice you can give is welcome. FWIW, the app

> > runs on 9iR0, 9iR2, 10g and XE.

> >

> > TIA

> > Tarby

>

> The current design is bad and based on what you've written your proposed

> design may be bad too. What is required to provide any advice is whether

> the 50 foreign keys could be collapsed into a single lookup table. It is

> always better to think vertically than horizontally.

> -- 

> Daniel A. Morgan

> http://www.psoug.org

> damorgan_at_x.washington.edu

> (replace x with u to respond)