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