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.
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;
What if there is another table called product_details, and link it to products with a one-to-one relationship, as illustrated.
mysql> INSERT INTO product_details VALUES (1001, ‘good one’);
mysql> SELECT products.productID,price,product_details.commentFROM productsJOIN product_details ON products.productID = product_details.productIDWHERE price < 5;
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.
To add a Foreign Key to supplierID
columns of the products
child table to the suppliers
parent table :
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);
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)
mysql> SELECT products.name, price, suppliers.nameFROM productsJOIN suppliers ON products.supplierID = suppliers.supplierIDWHERE price < 5;
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.
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));
mysql> INSERT INTO products_suppliers VALUES (1001, 501), (1001, 503), (1002, 501), (1003, 501), (1004, 502);
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";
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.
Very thankful to the community of NTU, who contribute such a good tutorial and valuable examples.
https://www.ntu.edu.sg/home/ehchua/programming/sql/MySQL_Beginner.html