Re: A normalisation issue

Hi Stratos, thanks for writing this:
> Hello,
> I want to ask you a question about normalisation.
> I have got a long table with 8 fields(mandatory) that always store a
> value, and 20 fields(optional) than can either get a NULL or non-NULL
> value.
>
> Is it better to split this table up into two tables, one having all
> the
> mandatory fields and the other one having all the optional fields? Or
> is
> it fine to leave this table as it is?
> Thank you.
>

Advantages to keeping in one table:
* The data architects on your team will applaud your grasp of their subject area. It's rare that you need to create a 1:1 relationship in your physical database.
* Your developers will appreciate not having to issue 2 select statements every time.
* You'll be taking advantage of Oracle's built-in architecture for handling a mixture of mandatory and optional columns.

Advantages to splitting:
* If most/all your optional fields are "large" varchar2 columns that start out holding just a few bytes each, then later a bulk load dumps 4000 bytes into each ... well, you'll get row chaining (not a good thing). * Sometimes, though admittedly only rarely, you can justify splitting data off in a 1:1 for security reasons.

My recommendation:
Keep them in one table. I have many, many tables in the situation that you describe, with a mixture of mandatory and optional columns. Oracle works very nicely in this mixed environment.

-- 
[:%s/Karsten Farrell/Oracle DBA/g]