[MySQL] Note: Relation: 1–1, 1-n, n-n, nest

pixabay.com

Relational database advantages include join table query, avoids data duplication, avoids inconsistent records, better security, cater for future requirements (by tech-ict.com).

Over the pass few years, I have built couple of E-commerce or content shared platform by MongoDB, and realized that it is not a ideal database for the architecture of complex relational tables(like user, permission, product or shop relations) and fast-moving features requirement(it causes inconsistent records/data type) . It happens every few months when new requirement come to us, dirty data correcting is like pull teeth from my ass.

Content

1. 1–1 relation
2. 1-n relation
3. n-n relation
4. Nest

Remark:

To have a default products and suppliers tables for this example

mysql> **SHOW DATABASES;**mysql> **USE yourdatabse;**mysql> CREATE TABLE product_details (productID INT UNSIGNED NOT NULL,-- same data type as the parent tablecomment TEXT NULL,-- up to 64KBPRIMARY KEY (productID),FOREIGN KEY (productID) REFERENCES products (productID));mysql> CREATE TABLE IF NOT EXISTS products (productID INT UNSIGNED NOT NULL AUTO_INCREMENT,productCode CHAR(3) NOT NULL DEFAULT '',name VARCHAR(30) NOT NULL DEFAULT '',quantity INT UNSIGNED NOT NULL DEFAULT 0,price DECIMAL(7,2) NOT NULL DEFAULT 99999.99,supplierID INT UNSIGNED NOT NULL DEFAULT 0,PRIMARY KEY (productID));

...

mysql> UPDATE products SET supplierID = 501;

1) 1–1 relation

What if there is another table called product_details, and link it to products with a one-to-one relationship, as illustrated.

Write foreign key column

mysql> INSERT INTO product_details VALUES (1001, ‘good one’);

Query relation by foreign key column

mysql> SELECT products.productID,price,product_details.commentFROM productsJOIN product_details ON products.productID = product_details.productIDWHERE price < 5;

2) 1–n relation

Let say each product has one supplier, and each supplier supplies one or more products, supplier-product is base in 1-n relation model . To implement 1-n relation, technically is as same as 1–1 relation by just make sure that no more than ROW has relation to same product.

Create foreign key column

To add a Foreign Key to supplierID columns of the products child table to the suppliers parent table :

1. add supplierID column with INT type in product table
2. set all the supplierID of the existing records

3. set supplierID as a foreign key column, by related products table to parent **suppliers table**mysql> ALTER TABLE products ADD COLUMN supplierID INT UNSIGNED NOT NULL;

mysql> UPDATE products SET supplierID = 501;

mysql> ALTER TABLE productsADD FOREIGN KEY (supplierID) REFERENCES suppliers (supplierID);

Write foreign key column

it is as same as writing an INT column

mysql> **INSERT INTO products VALUES (1001, 'PEN', 'Pen Red', 5000, 1.23, 503);**Query OK, 1 row affected (0.00 sec)

Query relation by foreign key column

mysql> SELECT products.name, price, suppliers.nameFROM productsJOIN suppliers ON products.supplierID = suppliers.supplierIDWHERE price < 5;

3) n–n relation

Suppose that a product has many suppliers; and a supplier supplies many products in a so-called many-to-many relationship. For a many to many relationship, it needs a third table called the junction table.

Create junction table products_suppliers

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

Insert relation

mysql> INSERT INTO products_suppliers VALUES (1001, 501), (1001, 503), (1002, 501), (1003, 501), (1004, 502);

Query 3 related table

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_suppliersJOIN products ON products_suppliers.productID = products.productIDJOIN suppliers ON products_suppliers.supplierID = suppliers.supplierIDWHERE price < 5 AND products.name = "Pen Red1" AND suppliers.name = "QQ Corp";

Summary

If you use many-to-many relationships, you have to introduce a JOIN table (or junction table) that holds foreign keys of both participating tables which further increases join operation costs.

You may also like

• MySQL Handbook
• [MySQL] Note: Fast Setup and running in Node.js
• [MySQL] Note: Create Admin User
• [MySQL] Note: Database CURD
• [MySQL] Note: Row CURD
• [MySQL] Note: Relation: 1–1, 1-n, n-n, nest

Very thankful to the community of NTU, who contribute such a good tutorial and valuable examples.

Reference:

https://www.ntu.edu.sg/home/ehchua/programming/sql/MySQL_Beginner.html