More Than One Tables

Our example so far involves only one table "products". A practical database contains many related tables.

Products have suppliers. If each product has one supplier, and each supplier supplies only one product (known as one-to-one relationship), we can simply add the supplier's data (name, address, phone number) into the products table. Suppose that each product has one supplier, and a supplier may supply zero or more products (known as one-to-many relationship). Putting the supplier's data into the products table results in duplication of data. This is because one supplier may supply many products, hence, the same supplier's data appear in many rows. This not only wastes the storage but also easily leads to inconsistency (as all duplicate data must be updated simultaneously). The situation is even more complicated if one product has many suppliers, and each supplier can supply many products, in a many-to-many relationship.

One-To-Many Relationship

Suppose that each product has one supplier, and each supplier supplies one or more products. We could create a table called suppliers to store suppliers' data (e.g., name, address and phone number). We create a column with unique value called supplierID to identify every suppliers. We set supplierID as the primary key for the table suppliers (to ensure uniqueness and facilitate fast search).

To relate the suppliers table to the products table, we add a new column into the products table - the supplierID. We then set the supplierID column of the products table as a foreign key references the supplierID column of the suppliers table to ensure the so-called referential integrity.

Database: southwindTable: suppliers
supplierIDINT	nameVARCHAR(3)	phoneCHAR(8)
501	ABC Traders	88881111
502	XYZ Company	88882222
503	QQ Corp	88883333

Database: southwindTable: products
productIDINT	productCodeCHAR(3)	nameVARCHAR(30)	quantityINT	priceDECIMAL(10,2)	supplierIDINT(Foreign Key)
2001	PEC	Pencil 3B	500	0.52	501
2002	PEC	Pencil 4B	200	0.62	501
2003	PEC	Pencil 5B	100	0.73	501
2004	PEC	Pencil 6B	500	0.47	502

We need to first create the suppliers table, because the products table references the suppliers table. The suppliers table is known as the parent table; while the products table is known as the child table in this relationship.

mysql> **USE southwind;**

mysql> **DROP TABLE IF EXISTS suppliers;**

mysql> **CREATE TABLE suppliers (
         supplierID  INT UNSIGNED  NOT NULL AUTO_INCREMENT, 
         name        VARCHAR(30)   NOT NULL DEFAULT '', 
         phone       CHAR(8)       NOT NULL DEFAULT '',
         PRIMARY KEY (supplierID)
       );**

mysql> **DESCRIBE suppliers;**
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| supplierID | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(30)      | NO   |     |         |                |
| phone      | char(8)          | NO   |     |         |                |
+------------+------------------+------+-----+---------+----------------+

mysql> **INSERT INTO suppliers VALUE
          (501, 'ABC Traders', '88881111'), 
          (502, 'XYZ Company', '88882222'), 
          (503, 'QQ Corp', '88883333');**

mysql> **SELECT * FROM suppliers;**
+------------+-------------+----------+
| supplierID | name        | phone    |
+------------+-------------+----------+
|        501 | ABC Traders | 88881111 |
|        502 | XYZ Company | 88882222 |
|        503 | QQ Corp     | 88883333 |
+------------+-------------+----------+

ALTER TABLE

Instead of deleting and re-creating the products table, we shall use "ALTER TABLE" to add a new column supplierID into the products table.

mysql> **ALTER TABLE products
       ADD COLUMN supplierID INT UNSIGNED NOT NULL;**
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> **DESCRIBE products;**
+-------------+------------------+------+-----+------------+----------------+
| Field       | Type             | Null | Key | Default    | Extra          |
+-------------+------------------+------+-----+------------+----------------+
| productID   | int(10) unsigned | NO   | PRI | NULL       | auto_increment |
| productCode | char(3)          | NO   |     |            |                |
| name        | varchar(30)      | NO   |     |            |                |
| quantity    | int(10) unsigned | NO   |     | 0          |                |
| price       | decimal(10,2)    | NO   |     | 9999999.99 |                |
| supplierID  | int(10) unsigned | NO   |     | NULL       |                |
+-------------+------------------+------+-----+------------+----------------+

Next, we shall add a foreign key constraint on the supplierID columns of the products child table to the suppliers parent table, to ensure that every supplierID in the products table always refers to a valid supplierID in the suppliers table - this is called referential integrity.

Before we can add the foreign key, we need to set the supplierID of the existing records in the products table to a valid supplierID in the suppliers table (say supplierID=501).

-- Set the supplierID of the existing records in "products" table to a VALID supplierID
--   of "suppliers" table
mysql> **UPDATE products SET supplierID = 501;**

-- Add a foreign key constrain
mysql> **ALTER TABLE products
       ADD FOREIGN KEY (supplierID) REFERENCES suppliers (supplierID);**

mysql> **DESCRIBE products;**
+-------------+------------------+------+-----+------------+----------------+
| Field       | Type             | Null | Key | Default    | Extra          |
+-------------+------------------+------+-----+------------+----------------+
  ......
| supplierID  | int(10) unsigned | NO   | MUL |            |                |
+-------------+------------------+------+-----+------------+----------------+

mysql> **UPDATE products SET supplierID = 502 WHERE productID  = 2004;**
  -- Choose a valid productID

mysql> **SELECT * FROM products;**
+-----------+-------------+-----------+----------+-------+------------+
| productID | productCode | name      | quantity | price | supplierID |
+-----------+-------------+-----------+----------+-------+------------+
|      2001 | PEC         | Pencil 3B |      500 |  0.52 |        501 |
|      2002 | PEC         | Pencil 4B |      200 |  0.62 |        501 |
|      2003 | PEC         | Pencil 5B |      100 |  0.73 |        501 |
|      2004 | PEC         | Pencil 6B |      500 |  0.47 |        502 |
+-----------+-------------+-----------+----------+-------+------------+

SELECT with JOIN

SELECT command can be used to query and join data from two related tables. For example, to list the product's name (in products table) and supplier's name (in suppliers table), we could join the two table via the two common supplierID columns:

-- ANSI style: JOIN ... ON ...
mysql> **SELECT products.name, price, suppliers.name 
       FROM products 
          JOIN suppliers ON products.supplierID = suppliers.supplierID
       WHERE price < 0.6;**
+-----------+-------+-------------+
| name      | price | name        |
+-----------+-------+-------------+
| Pencil 3B |  0.52 | ABC Traders |
| Pencil 6B |  0.47 | XYZ Company |
+-----------+-------+-------------+
    -- Need to use products.name and suppliers.name to differentiate the two "names"

-- Join via WHERE clause (lagacy and not recommended)
mysql> **SELECT products.name, price, suppliers.name 
       FROM products, suppliers 
       WHERE products.supplierID = suppliers.supplierID
          AND price < 0.6;**
+-----------+-------+-------------+
| name      | price | name        |
+-----------+-------+-------------+
| Pencil 3B |  0.52 | ABC Traders |
| Pencil 6B |  0.47 | XYZ Company |
+-----------+-------+-------------+

In the above query result, two of the columns have the same heading "name". We could create aliases for headings.

-- Use aliases for column names for display
mysql> **SELECT products.name AS `Product Name`, price, suppliers.name AS `Supplier Name` 
       FROM products 
          JOIN suppliers ON products.supplierID = suppliers.supplierID
       WHERE price < 0.6;**
+--------------+-------+---------------+
| Product Name | price | Supplier Name |
+--------------+-------+---------------+
| Pencil 3B    |  0.52 | ABC Traders   |
| Pencil 6B    |  0.47 | XYZ Company   |
+--------------+-------+---------------+

-- Use aliases for table names too
mysql> **SELECT p.name AS `Product Name`, p.price, s.name AS `Supplier Name` 
       FROM products AS p 
          JOIN suppliers AS s ON p.supplierID = s.supplierID
       WHERE p.price < 0.6;**

The database diagram is as illustrated. The link indicates a one-to-many relationship between products and suppliers.

Many-To-Many Relationship

Suppose that a product has many suppliers; and a supplier supplies many products in a so-called many-to-many relationship. The above solution breaks. You cannot include the supplierID in the products table, as you cannot determine the number of suppliers, and hence, the number of columns needed for the supplierIDs. Similarly, you cannot include the productID in the suppliers table, as you cannot determine the number of products.

To resolve this problem, you need to create a new table, known as a junction table (or joint table), to provide the linkage. Let's call the junction table products_suppliers, as illustrated.

Database: southwindTable: products_suppliers
productIDINT(Foreign Key)	supplierIDINT(Foreign Key)
2001	501
2002	501
2003	501
2004	502
2001	503

Database: southwindTable: suppliers
supplierIDINT	nameVARCHAR(30)	phoneCHAR(8)
501	ABC Traders	88881111
502	XYZ Company	88882222
503	QQ Corp	88883333

Database: southwindTable: products
productIDINT	productCodeCHAR(3)	nameVARCHAR(30)	quantityINT	priceDECIMAL(10,2)
2001	PEC	Pencil 3B	500	0.52
2002	PEC	Pencil 4B	200	0.62
2003	PEC	Pencil 5B	100	0.73
2004	PEC	Pencil 6B	500	0.47

Let's create the products_suppliers table. The primary key of the table consists of two columns: productID and supplierID, as their combination uniquely identifies each rows. This primary key is defined to ensure uniqueness. Two foreign keys are defined to set the constraint to the two parent tables.

mysql> **CREATE TABLE products_suppliers (
         productID   INT UNSIGNED  NOT NULL,
         supplierID  INT UNSIGNED  NOT NULL,
                     -- Same data types as the parent tables
         PRIMARY KEY (productID, supplierID),
                     -- uniqueness
         FOREIGN KEY (productID)  REFERENCES products  (productID),
         FOREIGN KEY (supplierID) REFERENCES suppliers (supplierID)
       );**

mysql> **DESCRIBE products_suppliers;**
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| productID  | int(10) unsigned | NO   | PRI | NULL    |       |
| supplierID | int(10) unsigned | NO   | PRI | NULL    |       |
+------------+------------------+------+-----+---------+-------+

mysql> **INSERT INTO products_suppliers VALUES (2001, 501), (2002, 501),
       (2003, 501), (2004, 502), (2001, 503);**
-- Values in the foreign-key columns (of the child table) must match 
--   valid values in the columns they reference (of the parent table)

mysql> **SELECT * FROM products_suppliers;**
+-----------+------------+
| productID | supplierID |
+-----------+------------+
|      2001 |        501 |
|      2002 |        501 |
|      2003 |        501 |
|      2004 |        502 |
|      2001 |        503 |
+-----------+------------+

Next, remove the supplierID column from the products table. (This column was added to establish the one-to-many relationship. It is no longer needed in the many-to-many relationship.)

Before this column can be removed, you need to remove the foreign key that builds on this column. To remove a key in MySQL, you need to know its constraint name, which was generated by the system. To find the constraint name, issue a "SHOW CREATE TABLE products" and take note of the foreign key's constraint name in the clause "CONSTRAINT constraint_name FOREIGN KEY ....". You can then drop the foreign key using "ALTER TABLE products DROP FOREIGN KEY _constraint_name_"

mysql> **SHOW CREATE TABLE products \G**
Create Table: CREATE TABLE `products` (
  `productID`   int(10) unsigned  NOT NULL AUTO_INCREMENT,
  `productCode` char(3)           NOT NULL DEFAULT '',
  `name`        varchar(30)       NOT NULL DEFAULT '',
  `quantity`    int(10) unsigned  NOT NULL DEFAULT '0',
  `price`       decimal(7,2)      NOT NULL DEFAULT '99999.99',
  `supplierID`  int(10) unsigned   NOT NULL DEFAULT '501',
  PRIMARY KEY (`productID`),
  KEY `supplierID` (`supplierID`),
  CONSTRAINT **`products_ibfk_1`** FOREIGN KEY (`supplierID`) 
     REFERENCES `suppliers` (`supplierID`)
) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=latin1

mysql> **ALTER TABLE products DROP FOREIGN KEY products_ibfk_1;**

mysql> **SHOW CREATE TABLE products \G**

Now, we can remove the column redundant supplierID column.

mysql> **ALTER TABLE products DROP supplierID;**

mysql> **DESC products;**

Querying

Similarly, we can use SELECT with JOIN to query data from the 3 tables, for examples,

mysql> **SELECT products.name AS `Product Name`, price, suppliers.name AS `Supplier Name`
       FROM products_suppliers 
          JOIN products  ON products_suppliers.productID = products.productID
          JOIN suppliers ON products_suppliers.supplierID = suppliers.supplierID
       WHERE price < 0.6;**
+--------------+-------+---------------+
| Product Name | price | Supplier Name |
+--------------+-------+---------------+
| Pencil 3B    |  0.52 | ABC Traders   |
| Pencil 3B    |  0.52 | QQ Corp       |
| Pencil 6B    |  0.47 | XYZ Company   |
+--------------+-------+---------------+

-- Define aliases for tablenames too 
mysql> **SELECT p.name AS `Product Name`, s.name AS `Supplier Name`
       FROM products_suppliers AS ps 
          JOIN products AS p ON ps.productID = p.productID
          JOIN suppliers AS s ON ps.supplierID = s.supplierID
       WHERE p.name = 'Pencil 3B';**
+--------------+---------------+
| Product Name | Supplier Name |
+--------------+---------------+
| Pencil 3B    | ABC Traders   |
| Pencil 3B    | QQ Corp       |
+--------------+---------------+

-- Using WHERE clause to join (legacy and not recommended)
mysql> **SELECT p.name AS `Product Name`, s.name AS `Supplier Name`
       FROM products AS p, products_suppliers AS ps, suppliers AS s
       WHERE p.productID = ps.productID
          AND ps.supplierID = s.supplierID
          AND s.name = 'ABC Traders';**
+--------------+---------------+
| Product Name | Supplier Name |
+--------------+---------------+
| Pencil 3B    | ABC Traders   |
| Pencil 4B    | ABC Traders   |
| Pencil 5B    | ABC Traders   |
+--------------+---------------+

The database diagram is as follows. Both products and suppliers tables exhibit a one-to-many relationship to the junction table. The many-to-many relationship is supported via the junction table.

One-to-one Relationship

Suppose that some products have optional data (e.g., photo, comment). Instead of keeping these optional data in the products table, it is more efficient to create another table called product_details, and link it to products with a one-to-one relationship, as illustrated.

mysql> **CREATE TABLE product_details (
          productID  INT UNSIGNED   NOT NULL,
                     -- same data type as the parent table
          comment    TEXT  NULL,
                     -- up to 64KB
          PRIMARY KEY (productID),
          FOREIGN KEY (productID) REFERENCES products (productID)
       );**

mysql> **DESCRIBE product_details;**
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| productID | int(10) unsigned | NO   | PRI | NULL    |       |
| comment   | text             | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+

mysql> **SHOW CREATE TABLE product_details \G**
*************************** 1\. row ***************************
       Table: product_details
Create Table: CREATE TABLE `product_details` (
  `productID`  int(10) unsigned  NOT NULL,
  `comment`    text,
  PRIMARY KEY (`productID`),
  CONSTRAINT `product_details_ibfk_1` FOREIGN KEY (`productID`) REFERENCES `products` (`productID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Backup and Restore

Backup: Before we conclude this example, let's run the mysqldump utility program to dump out (backup) the entire southwind database.

**(For Windows)**
-- Start a NEW "cmd"
> **cd _path-to-mysql-bin_**
> **mysqldump -u root -p --databases southwind > "d:\myProject\backup_southwind.sql"**

**(For Macs)**
-- Start a NEW "terminal"
$ **cd /usr/local/mysql/bin**
$ **./mysqldump -u root -p --databases southwind > ~/Documents/backup_southwind.sql**

Study the output file, which contains CREATE DATABASE, CREATE TABLE and INSERT statements to re-create the tables dumped.

The SYNTAX for the mysqldump utility program is as follows:

-- Dump selected databases with --databases option
> mysqldump -u _username_ -p --databases _database1Name_ [_database2Name_ ...] > _backupFile_.sql
-- Dump all databases in the server with --all-databases option, except mysql.user table (for security)
> mysqldump -u root -p --all-databases --ignore-table=mysql.user > _backupServer_.sql

-- Dump all the tables of a particular database
> mysqldump -u _username_ -p _databaseName_ > _backupFile_.sql
-- Dump selected tables of a particular database
> mysqldump -u _username_ -p _databaseName_ _table1Name_ [_table2Name_ ...] > _backupFile_.sql

Restore: The utility mysqldump produces a SQL script (consisting of CREATE TABLE and INSERT commands to re-create the tables and loading their data). You can restore from the backup by running the script either:

via the "source" command in an interactive client. For example, to restore the southwind backup earlier:

**(For Windows)**
-- Start a MySQL client
mysql> **source d:/myProject/backup_southwind.sql**
   -- Provide absolute or relative filename of the script
   -- Use Unix-style forward slash (/) as path separator

**(For Macs)**
-- Start a MySQL client
mysql> **source ~/Documents/backup_southwind.sql**

via the "batch mode" of the mysql client program by re-directing the input from the script:

**(For Windows)**
-- Start a NEW "cmd"
> **cd _path-to-mysql-bin_**
> **mysql -u root -p southwind < d:\myProject\backup_southwind.sql**

**(For Macs)**
-- Start a NEW "terminal"
$ **cd /usr/local/mysql/bin**
$ **./mysql -u root -p southwind < ~/Documents/backup_southwind.sql**

More Than One Tables