Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
 
Python, Lua and Tcl - public course schedule [here]
Private courses on your site - see [here]
Please ask about maintenance training for Perl, PHP, Java, C, C++, Ruby, MySQL and Linux / Tomcat systems
 
Many to many table mapping (Pivot tables)

In a well designed SQL table, you should design each row to contain consistent and non-repetitive information, and you should avoid having whole lists of values in a single field. If you potentially have lists of values in a single field, then you should split them out into another table (see Codd's principles of database normalisation). We describe this as a "one to many mapping"; an example of such a mapping might be a table of train services, where you main table relates to the departure information of a train and your second table relates to the arrival of the train at each place it calls along its route.

If you require a "many to many mapping", then the situation is slightly more complicated. As an example, let's take a requirement to hold books and authors in table(s) in a database. One Author may have written several books, and several authors may jointly be responsible for a book.

No matter how you try to sort out a many to many mapping with two normalised tables, you can't! Should you break the rules of normalisation? No - you should use an extra table that we'll call a pivot table.

DESIGNING YOUR PIVOT TABLE

Draw a diagram with a column of authors down the left hand side, and a column of book titles down the right hand side. Then draw a series of lines linking authors to books. You'll find some authors have lines drawn to 2 or more books and some books with likes from 2 or more authors. This is a clear sign that you need a pivot table.

A Pivot table is, in essence, a table of the LINES that you've drawn joining books to authors, and it consists of two vital columns - an author id and a book id; we also strongly suggest that you provide a unique key for each line in a third column.

Let's see an example:

-------+-----------------+
| aid | name |
-------+-----------------+
| 1 | Martin Brown |
| 2 | Joseph Hall |
| 3 | Randal Schwartz |
| 4 | Tom Phoenix |
-------+-----------------+

-------+----------------------------------------------+
| bid | title |
-------+----------------------------------------------+
| 1 | Perl - I didn't know you could do that |
| 2 | DeBugging Perl |
| 3 | Effective Perl Programming |
| 4 | Learning Perl |
| 5 | Learning Perl objects references and modules |
-------+----------------------------------------------+

-------+------+-----+
| bid | aid | pid |
-------+------+-----+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 3 |
| 3 | 3 | 4 |
| 4 | 3 | 5 |
| 4 | 4 | 6 |
| 5 | 3 | 7 |
| 5 | 4 | 8 |
-------+------+-----+

At this point, you may be finding it hard to know what matches to what! We have chosen to use the same column name for the same data in each of the tables - in other words we'll be matching "bid" in the book table to "bid" in the pivot table. You could also choose to use a different numbering sequence for each of the ids - for example start the authors at 10000, the books at 20000 and the pivot rows and 30000 then it would be clear straight away from a number what it was about.

Here's the SQL code we used to create those tables:

drop table if exists authors;
drop table if exists books;
drop table if exists pivots;
create table authors (
        aid int,
        name text);
create table books (
        bid int,
        title text);
create table pivots (
        bid int,
        aid int,
        pid int primary key not null auto_increment);

load data infile "/Library/WebServer/live_html/trainee/tuesday/authors.tab" into table authors
fields terminated by "\t" (aid, name);
        
load data infile "/Library/WebServer/live_html/trainee/tuesday/books.tab" into table books
fields terminated by "\t" (bid, title);
        
load data infile "/Library/WebServer/live_html/trainee/tuesday/pivots.tab" into table pivots
fields terminated by "\t" (bid, aid);

JOINING MANY TO MANY MAPPINGS VIA PIVOT TABLES

You can join pivot tables very easily using where clauses:

mysql> select * from authors,books,pivots where authors.aid = pivots.aid and books.bid = pivots.bid;
-------+-----------------+------+----------------------------------------------+------+------+-----+
| aid | name | bid | title | bid | aid | pid |
-------+-----------------+------+----------------------------------------------+------+------+-----+
| 1 | Martin Brown | 1 | Perl - I didn't know you could do that | 1 | 1 | 1 |
| 1 | Martin Brown | 2 | DeBugging Perl | 2 | 1 | 2 |
| 2 | Joseph Hall | 3 | Effective Perl Programming | 3 | 2 | 3 |
| 3 | Randal Schwartz | 3 | Effective Perl Programming | 3 | 3 | 4 |
| 3 | Randal Schwartz | 4 | Learning Perl | 4 | 3 | 5 |
| 4 | Tom Phoenix | 4 | Learning Perl | 4 | 4 | 6 |
| 3 | Randal Schwartz | 5 | Learning Perl objects references and modules | 5 | 3 | 7 |
| 4 | Tom Phoenix | 5 | Learning Perl objects references and modules | 5 | 4 | 8 |
-------+-----------------+------+----------------------------------------------+------+------+-----+

You can also use a join; in some database engines that's more efficient, but in MySQL it's said that the where clause example above is optimised and runs just as well. However, we recommend you use a join as it's much clearer what you're doing for the maintenance programmer later on.

mysql> select * from authors join pivots on authors.aid = pivots.aid join books on books.bid = pivots.bid;
-------+-----------------+------+------+-----+------+----------------------------------------------+
| aid | name | bid | aid | pid | bid | title |
+------+-----------------+------+------+-----+------+----------------------------------------------+
| 1 | Martin Brown | 1 | 1 | 1 | 1 | Perl - I didn't know you could do that |
| 1 | Martin Brown | 2 | 1 | 2 | 2 | DeBugging Perl |
| 2 | Joseph Hall | 3 | 2 | 3 | 3 | Effective Perl Programming |
| 3 | Randal Schwartz | 3 | 3 | 4 | 3 | Effective Perl Programming |
| 3 | Randal Schwartz | 4 | 3 | 5 | 4 | Learning Perl |
| 4 | Tom Phoenix | 4 | 4 | 6 | 4 | Learning Perl |
| 3 | Randal Schwartz | 5 | 3 | 7 | 5 | Learning Perl objects references and modules |
| 4 | Tom Phoenix | 5 | 4 | 8 | 5 | Learning Perl objects references and modules |
-------+-----------------+------+------+-----+------+----------------------------------------------+

RECORDS THAT HAVE NO MAPPING AT ALL

If you have specified authors who don't have any book records, or book records that don't have any authors, you won't see them appear in the examples above. If you use a left join (in recent MySQL releases you have a right join as well), then you can generate extra records for these "Orphans" and if you add a where clause such as
       WHERE authors.aid is NULL
you can select only orphan records.

In some situations, Orphan records are acceptable but in others you'll want to delete them. If you're in doubt in your particular circumstance, think it through with an example of your data


See also Designing an SQL database layout

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
  [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)
  [59] MySQL - Pivot tables - (2004-09-22)

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., 2018: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01225 708225 • FAX: 01225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

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