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.

Stratos wrote:

> 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.

If the two tables would have a 1:1 relationship ... it is one table:
Leave it that way.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

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]

seb6@bton.ac.uk (Stratos) wrote in message news:<6d926bff.0308110414.98c0e4a@posting.google.c om>...
> 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.

Are they logically separate entities? Are the optional fields
dependent on the mandatory fields? Lots of other similar question you
should ask on your way to designing the table or tables used to store
this information.

Then once you have the logical design done, you have to ask the hard
physical design questions such as: if the data is split into two
tables, will performance suffer for frequently used queries? Or is the
split useful in that backup and restore of data is easier? or...

IOW, with the vague question you asked, all we can say is IT DEPENDS!

HTH
ed

seb6@bton.ac.uk (Stratos) wrote

> 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?

IMO this has more to do with physical implementation of a normalised
design, than with the actual normalisation process itself. Answering
your question from a normalisation viewpoint is easy. Simply no.

However, there's also the physical design & implementation stage.

Tables can be split horizontally or vertically - and yeah, in real
world applications and db designs. Nothing academic or "theory only"
wrt these concepts.

Horizontal splitting is often used when dealing with VLTs (Very Large
Tables).

Vertical partitioning is less common and usually considered when
dealing with 100's of columns for a single entity (which could point
rather to a logical design solution instead of a physical design
solution). Or dealing with BLOBs/CLOBs.

Oracle supports horizontal partitioning via partitioned tables.
Partitioning is usually straight forward and can be fairly easy to
determine whether or not this will improve performance and data
administration.

Oracle supports vertical partitioning beyond merely creating two or
more tables with the same PK. It also offers table clusters (max of 32
tables in a cluster if I recall correctly).

IMO clustering is not as easy and tad more complex. Maybe because I
very seldom need to use it myself. BTW, Oracle themselves use clusters
in the Oracle data dictionary (although this is for performance
reasons and not because of vertical partitioning).

It makes only sense in doing this when there is a very specific
benefit and/or technical reasons.. keeping in mind the #1 criteria in
any rdbms is data integrity. And that could be compromised (instead of
single table, you now have 2 or more tables with the same primary
key).

Thus, the initial response is not to vertically partition a table,
just because it can be done. Which is exactly what you have seen in
response to your question.

I agree. There need to be darn good reason why you want to vertically
partition a table.. unless you can think of and demonstrate potential
problem areas which will impact on performance (or even data
management) that could require the need to partition vertically and
cluster?

BTW - this not a stupid question as it may seem to some at 1st glance.
You may just need such an approach in future and the fact that you
have considered this is IMO a good thing. Some people tend to only
consider the basics/obvious when dealing with the physical design of a
database. At least you thought a bit beyond that. Simply slapping a
normalised design onto Oracle does not guarantee performance.

--
Billy