MySQL - Pivot tables

Python and Tcl - public course schedule [here]
Private courses on your site - see [here]
Please ask about maintenance training for Perl, PHP, Lua, etc

If at all possible, you should normalise information in your database using Codd's principles of database normalisation. Basically, this means that you don't duplicate information, store information that can be recalculated based on other fields, or store more than one piece of information in a single cell of your table.

This is all well and good until you want to store (for example) information about a person with a list of his children; the solution is to use two tables, with one table being joined to the other when you select your data out of the database.

That's what we call a "one to many" mapping, and the situation becomes slightly more complex when it goes a stage further and we have a "many to many" mapping. In such a situation, the best solution is usually to provide a third table known as a pivot table.

The subject came up during yesterday's MySQL Course and I wrote an example that I've posted to the solutions centre here on this site. The example will appear on appropriate future courses, and the data is available for viewing on the module description page.

(written 2004-09-22, updated 2006-06-05)

Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles

S154 - MySQL - Designing an SQL Database System
  [4426] FileMaker Day to Unix Time conversion - (2015-02-15)
  [3494] Databases - when to treat the rules as guidelines - (2011-10-23)
  [3361] Blowing our own trumpet - MySQL resources - (2011-07-18)
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [2749] Delegate Question - defining MySQL table relationships as you create the tables - (2010-05-02)
  [2204] Images in a database? How big is a database? (MySQL) - (2009-05-28)
  [2085] MySQL - licensing issues, even with using the name - (2009-03-16)
  [2053] What a difference a MySQL Index made - (2009-02-25)
  [1771] More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24)
  [1575] Database design for a shopping application (MySQL) - (2008-03-15)
  [1423] MySQL - table design and initial testing example - (2007-11-06)
  [945] Code quality counts - (2006-11-26)
  [937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
  [918] Databases needn't be frightening, hard or expensive - (2006-11-08)
  [666] Database design - get it right from first principles - (2006-04-02)
  [515] MySQL - an FAQ - (2005-12-03)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [375] Oops - I got my initial database design wrong - (2005-07-12)
  [361] Binary Large Objects or bars - (2005-06-27)

Back to
Call Centres, selling, and a pause for thought

Previous and next
or
Horse's mouth home

Forward to
Birth Notice

Some other Articles

Almost like old times
Technical Open House
Python is a fabulous language
Birth Notice
MySQL - Pivot tables
Call Centres, selling, and a pause for thought
Posting 1000 letters!
Dress Code
Evening classes to learn PHP
PHP and natural sorting

4759 posts, page by page

Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96 at 50 posts per page

This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).

You can Add a comment or ranking to this page

Like this? ??