It is quiet obvious,
one person can have more than one order
one order can have more than one product
one product can have multiple items
I hope this is easy to imagine.
Table Description
mysql> DESC PERSON;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| NAME | varchar(15) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> DESC ORDERS;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| PERSON_ID | int(11) | NO | MUL | NULL | |
| NAME | varchar(15) | NO | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
DDL
CREATE TABLE IF NOT EXISTS `PERSON` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(15) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT ;
CREATE TABLE IF NOT EXISTS `ORDERS` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`PERSON_ID` int(11) NOT NULL,
`NAME` varchar(15) NOT NULL,
PRIMARY KEY (`ID`),
KEY `PERSON_ID` (`PERSON_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT ;
ALTER TABLE `ORDERS`
ADD CONSTRAINT `ORDERS_ibfk_1` FOREIGN KEY (`PERSON_ID`) REFERENCES `PERSON` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
DML
INSERT INTO `PERSON` (`ID`, `NAME`) VALUES (2, 'TIFFANY');
INSERT INTO `ORDERS` (`ID`, `PERSON_ID`, `NAME`) VALUES
(1, 2, 'GROCERY'),
(2, 2, 'COSMETICS');
Table Content
mysql> SELECT * FROM PERSON;
+----+---------+
| ID | NAME |
+----+---------+
| 2 | TIFFANY |
+----+---------+
1 row in set (0.00 sec)
mysql> SELECT * FROM ORDERS;
+----+-----------+-----------+
| ID | PERSON_ID | NAME |
+----+-----------+-----------+
| 1 | 2 | GROCERY |
| 2 | 2 | COSMETICS |
+----+-----------+-----------+
2 rows in set (0.00 sec)
Delation of Cascaded Records
mysql> DELETE FROM PERSON WHERE ID=2;
Query OK, 1 row affected (0.04 sec)
mysql> SELECT * FROM ORDERS;
Empty set (0.00 sec)
mysql> SELECT * FROM PERSON;
Empty set (0.00 sec)
