"zorders" 4 Table Database example

Example ERD and basic commands to create tables

General ERD (Entity Relationship Diagram) aka Schema

So, to add to our zproducts table, we make a slightly more complete Design with 4 tables
Some reminders about design
- Only one relationship between any 2 tables
- Join tables are needed where two tables need multiple values of the other: ie
  - a order in the zorders table can have more than one product
  - a product in the zproducts table can be on more than one order

zorders Database design

SQL code to generate additional tables

    USE STUusername;
    DROP TABLE IF EXISTS zproducts;  (just to start clean)

    CREATE TABLE zorders (
    /*  This is a comment */
    order_id INT UNSIGNED auto_increment PRIMARY KEY,
    date_ordered DATETIME,
    customer_id INT UNSIGNED,
    order_num VARCHAR(30),
    UNIQUE KEY(order_num)
    ) engine = InnoDB CHARSET utf8mb4 COLLATE = utf8mb4_unicode_ci;

    CREATE TABLE zcustomers (
    customer_id INT UNSIGNED auto_increment PRIMARY KEY,
    fname VARCHAR(30),
    lname VARCHAR(40),
    state varchar(3)
    ) engine = InnoDB CHARSET utf8mb4 COLLATE = utf8mb4_unicode_ci;

    CREATE TABLE zproducts (
    product_id INT UNSIGNED auto_increment PRIMARY KEY,
    prod_name VARCHAR(30),
    prod_desc VARCHAR(255),
    quantity INT,
    price DECIMAL(10,2),
    SKU INT UNSIGNED,
    UNIQUE KEY(SKU)
    ) engine = InnoDB CHARSET utf8mb4 COLLATE = utf8mb4_unicode_ci;

    CREATE TABLE zorders_products (
    product_id INT UNSIGNED,
    order_id INT UNSIGNED,
    UNIQUE KEY(product_id,order_id),
    saleprice DECIMAL(10,2),
    quantity INT
    ) engine = InnoDB CHARSET utf8mb4 COLLATE = utf8mb4_unicode_ci;

    ALTER TABLE zorders ADD FOREIGN KEY (customer_id) 
    REFERENCES zcustomers(customer_id);

    ALTER TABLE zorders_products ADD FOREIGN KEY (product_id) 
    REFERENCES zproducts(product_id);

    ALTER TABLE zorders_products ADD FOREIGN KEY (order_id) 
    REFERENCES zorders(order_id);

Possible Problems with above design

inconsistent naming practices in zproducts
- should be prod_price and prod_quantity etc.
Would need additional tables to handle inventory properly
May want to add a notes section for the zorders, or the zorders_products table
zproducts should have a link to an image location
the Foreign Keys (FKs) do NOT have an ON DELETE option, which could cause issues when deleting values from tables
probably quite a few more

Do inserts for two more tables

    # This uses several built in MySQL functions to set dates
    INSERT INTO zorders (date_ordered) values
    (NOW()),
    (DATE_SUB(NOW(),INTERVAL 5 Day)),
    (DATE_ADD(NOW(),INTERVAL 1 month)),
    ('2018-09-23');

    INSERT INTO zproducts (prod_name,quantity,price)
    VALUES
    ('fusebox',23,23.95),
    ('conduit',123,3.95),
    ('monitor',34,112.95),
    ('electrical tape',345,2.97);

    # Note if you deleted product_id 1 from zproducts earlier, this will not work
    INSERT INTO zorders_products(order_id,product_id,quantity) VALUES
    (1,1,4),
    (1,3,2),
    (1,4,10),
    (2,4,4),
    (3,2,2),
    (3,4,25),
    (4,1,5),
    (4,2,3),
    (4,3,2),
    (4,4,55);

Query for Three tables

we can not include zcustomers as it has no data yet
When designing a query the FROM line is your first job
This query does a "cross product" query, it gives every possible combination as it does not know how to link things up (so we have 4 * 4 * 11 or 176 results, instead of 11)
```
# AVOID DOING THIS!!
SELECT *
FROM zorders,zorders_products,zproducts;
```

To properly set up the FROM line:

We need to list the tables involved
Explain how they will be connected
specify which fields from which tables connect (PK to FK)

Also handy to create aliases to simplify the rest of the query

# This gives the first table, zorders, the alias "zo"
# We then JOIN (specifically an INNER JOIN) zorders_products to our list, aliased as "zop"
# "zop" is now connected by using ON()
# so sql will only show us results here the primary key from zorders MATCHES the product_id in zorders_products
FROM zorders zo JOIN zorders_products zop ON(zo.order_id = zop.order_id)
# Now, we JOIN zproducts to our list as "zp" in the same manner
JOIN zproducts zp ON(zp.product_id = zop.product_id)
# at this point, we now have all three tables set up in our from line

Now that we have the FROM line, we can decide which fields we want to see

    SELECT zp.prod_name, zop.quantity, zo.date_ordered
    FROM zorders zo JOIN zorders_products zop ON(zo.order_id = zop.order_id)
    JOIN zproducts zp ON(zp.product_id = zop.product_id);

And once we know this is correct, we can add additional fields, WHERE clauses and ORDER BY to filter/sort for the exact data we want

Rearranging the order of tables in the FROM line

    /* This adds the order_id so you can tell which order goes to which product
       and sorts it by date ordered. Note, adding zo.order_id to the ORDER BY
       will not do anything, as right now we only have ONE order per date. */

    SELECT zp.prod_name, zop.quantity, zo.date_ordered,zo.order_id
    FROM zorders zo JOIN zorders_products zop ON(zo.order_id = zop.order_id)
        JOIN zproducts zp ON(zp.product_id = zop.product_id)
    ORDER BY zo.date_ordered;

    /* You can usually change the order of tables in the FROM line and the
        query should work, especially if just using JOIN (INNER JOIN).
        When using LEFT JOIN or RIGHT JOIN  the order of tables DOES matter. */

    SELECT zp.prod_name, zop.quantity, zo.date_ordered,zo.order_id
    FROM zproducts zp JOIN zorders_products zop ON (zop.product_id = zp.product_id)
        JOIN zorders zo ON (zo.order_id = zop.order_id)
    ORDER BY zo.date_ordered, zo.order_id;

Adding Data to, and JOINing the Fourth Table

Right now, we are using JOIN which is short for INNER JOIN
If we have two JOINs (as above) we will only see a record if all JOIN conditions are met (so if a product has no order, or an order has no products, they will not show up)
To demonstrate this, we will put some customers into the "zcustomers" table

We will JOIN them to the list of tables in our FROM line using:

JOIN zcustomers zc ON(zc.customer_id = zo.customer_id)

SELECT zc.fname, zc.lname,zp.prod_name, zop.quantity, zo.date_ordered, zo.order_id
FROM zorders zo 
JOIN zorders_products zop ON(zo.order_id = zop.order_id)
JOIN zproducts zp ON(zp.product_id = zop.product_id)
JOIN zcustomers zc ON(zc.customer_id = zo.customer_id)
ORDER BY zo.date_ordered;

But as there are NO customer_id's in zcustomers (PK) and no customer_id in zorders (FK) yet, we'll get zero results

#  First add customers to the zcustomers table
INSERT INTO zcustomers (fname,lname,state) values
('Phil','Waclawski','AZ'),
('Doug','Waclawski','AK'),
('Diane','Waclawski','MI'),
('Robbert','Waclawski','MI');

# Now our query that JOIN's zcustomers
# Note, we also add the fname and lname fields to the select line
# Yet this will STILL give us ZERO RESULTS due to no customer_id values in zorders

SELECT zc.fname, zc.lname,zp.prod_name, zop.quantity, zo.date_ordered, zo.order_id
FROM zorders zo 
JOIN zorders_products zop ON (zo.order_id = zop.order_id)
JOIN zproducts zp ON (zp.product_id = zop.product_id)
JOIN zcustomers zc ON (zc.customer_id = zo.customer_id)
ORDER BY zo.date_ordered;

Updating zorders to have customers

    UPDATE zorders set customer_id = 1 WHERE order_id = 2;
    UPDATE zorders set customer_id = 2 WHERE order_id = 1;
    UPDATE zorders set customer_id = 4 WHERE order_id = 3;
    UPDATE zorders set customer_id = 4 WHERE order_id = 4;

    # Now re-run the above query, and you'll get your 10 expected results
    # As customer_id 3 has no orders, they do not show up at all as every JOIN in
    # the query has to match or the entire record is not shown

Additional Work

Add two more products

    INSERT INTO zproducts (prod_name,quantity,price)
    VALUES
    ('50Amp Breaker',22,12.99),
    ('Lightning Rod',54,34.55);

    # If we run the big query, none of these products show up, as they are not linked to an order

Add entry into zorders_products

    # the order of the fields is different in this example
    # product_id is first, then order_id, you can specify
    # which fields and what order. Any fields that are allowed to
    # be null can be omitted. As the PK (primary key) is set
    # auto_increment it can be left out from the INSERT.
    INSERT INTO zorders_products (product_id,order_id,quantity)
    VALUES (5,4,12);

    # Now there is a link between zorders (order_id 4) and zproducts (product_id 5)
    # The big query now shows 5 different items for order_id 4, including the 50Amp Breaker

Add another item to order 3 for zorders_products

    # adds product 6 (lightning rod) to order 3
    INSERT INTO zorders_products (product_id,order_id,quantity)
    VALUES (6,3,1);

    # run the big query above, and now you see that "lightning rod" shows up

Delete the item from Order 3

DELETE is a powerful, potentially dangerous command
Usually best to start it out as a SELECT * before you turn it to a DELETE
This way you can see what records are being deleted before you do

Remember, there is no "UNDO" unless you are using TRANSACTIONs

# This would wipe out the entire table data, as there is no `WHERE`

DELETE FROM zorders_products;  #BAD, if you were not using a TRANSACTION you have a good chance of being fired

# This wipes out the entire order 3

DELETE FROM zorders_products WHERE order_id = 3;  #BAD

# This wipes out product 6 from EVERY order

DELETE FROM zorders_products WHERE product_id = 6; #BAD

# Finally, we only wipe out product 6 from order 3

DELETE FROM zorders_products 
WHERE order_id = 3 AND
product_id = 6;

# To test of these, replace "DELETE"  with "SELECT *"

SELECT * FROM zorders_products
WHERE order_id = 3 AND
product_id = 6;

Additional SELECT queries

To get the total number of rolls of "electrical tape" sold, we can use the SUM() function and a WHERE condition

SELECT zp.prod_name, sum(zop.quantity) AS "Total Sold"
FROM zorders zo JOIN zorders_products zop ON (zo.order_id = zop.order_id)
JOIN zproducts zp ON (zp.product_id = zop.product_id)
WHERE zp.prod_name = "electrical tape";

If you leave off the above "WHERE" clause, you get a total of ALL products, and it displays the first prod_name, even though the total is for ALL products

If you would like to get the total for EACH product, we need to use a "GROUP BY"

SELECT zp.prod_name, sum(zop.quantity) as "Total Sold"
FROM zorders zo 
JOIN zorders_products zop ON (zo.order_id = zop.order_id)
JOIN zproducts zp ON (zp.product_id = zop.product_id)
GROUP BY zp.product_id;

To find the number of orders that included electrical tape, we use the COUNT() function

SELECT zp.prod_name, count(zp.prod_name)
FROM zorders zo JOIN zorders_products zop ON (zo.order_id = zop.order_id)
JOIN zproducts zp ON (zp.product_id = zop.product_id)
WHERE zp.prod_name = "electrical tape";

Queries can do many many many more things.