Please follow this link to find out about our solutions and this link to go to our solutions centre home page

Should you ALWAYS Normalise your data??

"Not really rules - more a set of guidelines".  A quotation, 
concerning the Pirate's code from the film 
"Pirates of the Caribbean".

It strikes me that the "rules" of data normalisation in a 
database system such as MySQL should be treated more as 
guidelines too. Newcomers to SQL should learn and understand 
the principles of data normalisation, and apply them; they 
make for a clear data structure, eliminate needless data 
repetition, and provide a maintainable structure.

BUT - there are times that you can benefit from denormalising 
your data - often in terms of increasing performance.

Example 1.

A very busy forum, where each post was originally stored in a 
table with fields including:
- Poster ID
- Title
- Post id
- Post Content
The majority of selects do NOT require the Post content field 
to be returned, since users will look through many hundreds 
of titles before deciding which post to view, but the tablin 
in its original (normalised) form was slow to access because 
of all the content data.

Solution - Split the table into two, which can be joined 
through the Post id. Both tables will contain the same number 
of rows, but the listing of titles will be very much more 
efficient at the (slight) cost of looking at complete posts 
being a fraction slower.

Example 2.

Our book database, which comprises some 5 tables:
Books
Authors
Publishers
Subjects
and a table to join multiple books to a single author and 
vice versa.  Quite a complex scheme, with a 5 way join which 
(under version 3 of MySQL) ran quite slowly.

Solution - run the 5 way join once on all the data each time 
it changes (we don't add books to our library more than once 
or twice a week), and save the output from the join into a 
table in its own right. The hundreds (or perhaps thousands) 
of references to our book information made between each 
update are then made with a minimal compute need.

It is important to note that in both of these examples, the 
implementer of the scheme has been very careful to consider 
data maintainance and integrity issues with his decision to 
de-normalise, and concluded that the benefits of the scheme 
implemented outweight the additional issues raised.


See also More complex (My)SQL