Hackernoon logo[MySQL] Note: Relation: 1–1, 1-n, n-n, nest by@peterchang_82818

[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 table
comment TEXT NULL,
-- up to 64KB
PRIMARY 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.comment
FROM products
JOIN product_details ON products.productID = product_details.productID
WHERE 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 tablemysql> ALTER TABLE products
     ADD COLUMN supplierID INT UNSIGNED NOT NULL;
mysql> UPDATE products SET supplierID = 501;
mysql> ALTER TABLE products
ADD 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.name 
FROM products
JOIN suppliers ON products.supplierID = suppliers.supplierID
WHERE 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 tables
PRIMARY KEY (productID, supplierID),
-- uniqueness
FOREIGN 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_suppliers
JOIN products ON products_suppliers.productID = products.productID
JOIN suppliers ON products_suppliers.supplierID = suppliers.supplierID
WHERE 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

Tags

Join Hacker Noon

Create your free account to unlock your custom reading experience.