[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

• 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

Hacker Noon is how hackers start their afternoons. We’re a part of the @AMI family. We are now accepting submissions and happy to discuss advertising & sponsorship opportunities.

If you enjoyed this story, we recommend reading our latest tech stories and trending tech stories. Until next time, don’t take the realities of the world for granted!