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