paint-brush
[MySQL] Note: Relation: 1–1, 1-n, n-n, nestby@peterchang_82818
46,995 reads
46,995 reads

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

by December 25th, 2016
Read on Terminal Reader
Read this story w/o Javascript
tldt arrow

Too Long; Didn't Read

Over the pass few years, I have built couple of E-commerce or content shared platform by <strong><em>MongoDB</em></strong>, 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)&nbsp;. It happens every few months when new requirement come to us, dirty data correcting is like pull teeth from my ass.

Company Mentioned

Mention Thumbnail
featured image - [MySQL] Note: Relation: 1–1, 1-n, n-n, nest
 HackerNoon profile picture

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

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