pixabay.com Relational database advantages include , avoids data duplication, avoids inconsistent records, better security, cater for future requirements ( ). join table query by tech-ict.com Over the pass few years, I have built couple of E-commerce or content shared platform by , 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. MongoDB Content 1–1 relation 1-n relation n-n relation Nest Remark: To have a default products and suppliers tables for this example mysql> **SHOW DATABASES;**mysql> **USE yourdatabse;**mysql> 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)); 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 , and link it to products with a one-to-one relationship, as illustrated. product_details 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 products WHERE price < 5; JOIN product_details ON products.productID = product_details.productID 2) 1–n relation Let say each product has one supplier, and each supplier supplies one or more products, . 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. supplier-product is base in 1-n relation model Create foreign key column To add a to columns of the child table to the parent table : Foreign Key supplierID products suppliers add column with INT type in product table supplierID set all the of the existing records supplierID set as a foreign key column, by related table to parent ** **mysql> supplierID products suppliers table 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 products WHERE price < 5; JOIN suppliers ON products.supplierID =  suppliers.supplierID 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 with to query data from the 3 tables, for examples, SELECT JOIN 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 table (or junction table) that holds foreign keys of both participating tables which further increases join operation costs. JOIN 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

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

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

Too Long; Didn't Read

Company Mentioned

Peter Chang

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

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

Too Long; Didn't Read

Company Mentioned

Peter Chang

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES