Re: A normalisation issue

seb6_at_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