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
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] - ()
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.