Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
 
For 2023 (and 2024 ...) - we are now fully retired from IT training.
We have made many, many friends over 25 years of teaching about Python, Tcl, Perl, PHP, Lua, Java, C and C++ - and MySQL, Linux and Solaris/SunOS too. Our training notes are now very much out of date, but due to upward compatability most of our examples remain operational and even relevant ad you are welcome to make us if them "as seen" and at your own risk.

Lisa and I (Graham) now live in what was our training centre in Melksham - happy to meet with former delegates here - but do check ahead before coming round. We are far from inactive - rather, enjoying the times that we are retired but still healthy enough in mind and body to be active!

I am also active in many other area and still look after a lot of web sites - you can find an index ((here))
When to denormalise your MySQL data

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 table in 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 maintenance 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

Please note that articles in this section of our web site were current and correct to the best of our ability when published, but by the nature of our business may go out of date quite quickly. The quoting of a price, contract term or any other information in this area of our website is NOT an offer to supply now on those terms - please check back via our main web site

Related Material

MySQL - Designing an SQL Database System
  [59] - ()
  [361] - ()
  [375] - ()
  [494] - ()
  [515] - ()
  [666] - ()
  [918] - ()
  [937] - ()
  [945] - ()
  [1423] - ()
  [1575] - ()
  [1771] - ()
  [2053] - ()
  [2085] - ()
  [2204] - ()
  [2749] - ()
  [3270] - ()
  [3361] - ()
  [3494] - ()
  [4426] - ()

More MySQL commands
  [158] - ()
  [159] - ()
  [279] - ()
  [449] - ()
  [494] - ()
  [502] - ()
  [513] - ()
  [515] - ()
  [517] - ()
  [567] - ()
  [572] - ()
  [581] - ()
  [591] - ()
  [673] - ()
  [1213] - ()
  [1235] - ()
  [1331] - ()
  [1574] - ()
  [1735] - ()
  [1904] - ()
  [2110] - ()
  [2259] - ()
  [2448] - ()
  [2643] - ()
  [2644] - ()
  [2645] - ()
  [2647] - ()
  [3061] - ()
  [3270] - ()
  [4481] - ()

resource index - MySQL
Solutions centre home page

You'll find shorter technical items at The Horse's Mouth and delegate's questions answered at the Opentalk forum.

At Well House Consultants, we provide training courses on subjects such as Ruby, Lua, Perl, Python, Linux, C, C++, Tcl/Tk, Tomcat, PHP and MySQL. We're asked (and answer) many questions, and answers to those which are of general interest are published in this area of our site.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2024: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/solutions/mysql-wh ... -data.html • PAGE BUILT: Wed Mar 28 07:47:11 2012 • BUILD SYSTEM: wizard