Database Normalization – Ensure No Data Loss When Splitting Tables

denormalizationnormalization

This is w.r.t Database Normalisation, when I have a redundant table that causes anomalies, I would want to consider splitting it in smaller tables as per the NF rules.

BUT

Say I split a big table into two eg. Student and Marks Table my questions is —

How do I know I've done the right split?

Or to put it in another way,

How can I cross check if I haven't lost any information after the split i.e. I can still do a join on both tables and get my un-normalized table back?

This is easy to inspect visually when you have a small number of attributes, but what if the table is really huge.

How can I ensure that I've split the table in a right manner?

When I was in college my professor spoke of some equation which must always be true whenever you split tables, that ensures that the split is done without loss of any information. And by doing the right joins we can get the big dirty redundant table back.

Best Answer

A classical theorem in normalization theory says that you have a lossless join (that is a decomposition of a relation such that when you perform the natural join of the decomposed relations you obtain the original relation) in the case of two relations if and only if the intersection of the attributes of the relations contains a candidate key for one of the two relations.

This result can be generalized to a decomposition with any number of relations.

Related Solutions

Database Design – Relate Two Tables to a Third Table

Here is a suggestion so you can enforce the constraints you want declaratively. (I've simplified the table names a bit, removed the bridge_ prefix.)

We remove footnote_num from:

Table:      a_ref         -- was named:  bridge_a_reference
     a_id, 
     ref_id
Primary key: 
     (a_id, ref_id)
Foreign keys:
     a_id  -> a
     ref_id -> reference

We add this table - which will basically store only those rows from a_ref with footnote, those you want to add children into the b_ref:

Table:      a_ref_with_footnote 
    a_id, 
    ref_id,
    footnote_num
Primary key: 
    (a_id, ref_id)
Unique key:
    (a_id, footnote_num)
Foreign keys: 
    (a_id, ref_id)  -> a_ref

And finally the 3rd table stays as in your design except the foreign keys which now reference the intermediate table (a_ref_with_footnote):

 Table:     b_ref         -- was named: bridge_b_reference:
     a_id, 
     b_id, 
     ref_id,
 Primary key: 
     (b_id, ref_id)
 Foreign keys: 
     (a_id, b_id) -> b
     (a_id, ref_id) -> a_ref_with_footnote

Need some recommendations on organizing data

Using a surrogate key as a primary key for tables is a very common approach and it seems to fit your situation quite well. The foreign key in local maxima can then be a single column referencing the surrogate key.

If the relationship between the definition of the run and the outcome of the run is a one-to-one relationship, then you should at first consider having them in the same table, and only split them up if you feel it's more natural, convenient, or better for performance.

Best Answer

Related Solutions

Database Design – Relate Two Tables to a Third Table

Need some recommendations on organizing data

Related Question