MySQL - Pivot tables
Archive - Originally posted on "The Horse's Mouth" - 2004-09-22 06:38:55 - Graham EllisIf 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.