Re: Table Normalization - 1 table w/ many columns, or many tables w/ fewer columns?

Sybrand,

Agree 100%. Where is uncle Ted when we need him?

Does this mean you have a degree of sympathy with those of us who have to suffer the imposition of Peoplesoft?

Paul

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:u61hrd7s1fe0b_at_corp.supernews.com...
>
> "AEG" <agibbons_at_erols.com> wrote in message
> news:u61aq5fitj7b4b_at_corp.supernews.com...
> > I have a question an experienced DBA might be able to shed some light
on.
> > Any words of wisdom would be much appreciated.
> > Here's the situation:
> >
> > I have an Oracle database table with 120 columns. It has a primary key
of
> 4
> > columns
> > and it is to be used as the main table in a survey application.
> >
> > Table A
> >
> > A DBA/developer has suggested it would be more efficient to split the
> table
> > into 3 tables,
> > each with the 4 primary key columns, and define 1:1 relations between
them
> > as follows:
> >
> > Table A1
> > |
> > Table A2
> > |
> > Table A3
> >
> > Are there a good reasons for doing this? Speed, table sizes/extents,
> etc.?
> > To me, this seems unnecessarily complex as Oracle should be able to
handle
> > a table with many columns without difficulty. And splitting the tables
> > means
> > managing more code within forms and reports, etc.
> > Am I missing something here?
> >
> > It seems like the 1 larger table would provide easier access to data
when
> > writing
> > queries and creating forms/reports. Perhaps a little less efficient in
> > terms of table
> > extents, but more efficient in terms of managing the overall
application.
> >
> > Please excuse me if I have asked the obvious.
> >
> > AEG
> >
> >
> >
>
> Without having further details on the contents of those 120 columns, my
gut
> feeling is your design is completely unnormalized. That is definitely a
bad
> thing, as you will suffer from update and delete anomalies.
> My experience is a table seldomly has 120 columns.
> Theories on what normalizations is and how you should normalize do exist
and
> have been described in many books. You should normalize using those
theories
> to at least the 3rd normal form, instead of just randomly cutting your
> design in 3 or 4 tables. This will make terms worse.
> Your remarks on efficiency, are given the state of database and server
> technology, incorrect. Having a normalized design is much more important
> than having to join a few tables instead of using one table. If you really
> think that is a concern you should either buy a more powerful server or
> alternatively put your complete database in one table for 'reasons of
> efficiency'
>
> Regards
>
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>
> to reply remove '-verwijderdit' from my e-mail address
>
>
>
Received on Wed Feb 06 2002 - 16:17:08 CST