Ecommerce Database - Using PHP and MySQL BLOB (ER diagram is below)

Image thumbnails are 0.8% of original size; you can download full resolution by right-clicking/long tapping (depending on your device) the thumbnail and choosing "save as" or similar

First, let's do a complete overview of the database, which is a 5-table join but does NOT show childless parent rows:

select
  customers.customerID
 ,firstName
 ,lastName
 ,email
 ,password
 ,orders.orderID
 ,orderDate
 ,categories.categoryID
 ,categories.name "category_name"
 ,products.productID
 ,products.name
 ,orderDetails.quantity
 ,price
 ,orderDetails.quantity * price "lineTotal"
 ,products.product_image 
from 
  customers
 ,orders
 ,products
 ,orderDetails
 ,categories 
where
  orders.customerID=customers.customerID 
  and orders.orderID=orderDetails.orderID 
  and orderDetails.productID=products.productID 
  and products.categoryID=categories.categoryID

Database Overview, no childless parent rows shown

Customer ID	First Name	Last Name	Email	Password	Order Id	Order Date	Category ID	Category Name	Product ID	Product Name	Quantity	Price, US$	Line Item Total	Product Image
1	Chin	Lee	foo@bar	secret	1	2024-03-03 15:00:33	1	Graphic Images	1	E-Book clip art	1	$100.23	$100.23
2	Cindy	Shin	foo@bar	secret	4	2024-03-02 14:30:13	4	Photography	5	Model Railroad	3	$22.34	$67.02
3	Joe	Schmo	me@abc.net	changeMe!	5	2024-03-03 11:21:52	1	Graphic Images	1	E-Book clip art	367	$100.23	$36784.41
3	Joe	Schmo	me@abc.net	changeMe!	5	2024-03-03 11:21:52	3	Other	2	Milk	744	$10.32	$7678.08
3	Joe	Schmo	me@abc.net	changeMe!	5	2024-03-03 11:21:52	3	Other	3	Light Bulb, old fashioned incandescent (tungsten filament), generates lots of heat as well as light!	4	$55.77	$223.08
3	Joe	Schmo	me@abc.net	changeMe!	5	2024-03-03 11:21:52	3	Other	4	Color Wheel	8	$55.77	$446.16
3	Joe	Schmo	me@abc.net	changeMe!	5	2024-03-03 11:21:52	4	Photography	5	Model Railroad	43	$22.34	$960.62
3	Joe	Schmo	me@abc.net	changeMe!	5	2024-03-03 11:21:52	5	Widgets	6	Extension Cord	77	$22.34	$1720.18

Next, let's join customers (parent) to orders (child), showing customers with no orders having NULL for Order ID and Date:

select
  customers.customerID
  ,firstName
  ,lastName
  ,email
  ,password
  ,orderID
  ,orderDate 
from 
customers left outer join orders 
on orders.customerID=customers.customerID

Orders by Customer

Next, let's join categories (parent) to products (child), showing categories with no products as NULL for product name:

select
   categories.categoryID
  ,categories.name "categoryName"
  ,products.productID
  ,products.name "productName" 
  ,products.product_image
from 
categories left outer join products 
on categories.categoryID=products.categoryID

Products by Category

Category ID	Category Name	Product ID	Product Name	Product Image
1	Graphic Images	1	E-Book clip art
3	Other	2	Milk
3	Other	3	Light Bulb, old fashioned incandescent (tungsten filament), generates lots of heat as well as light!
3	Other	4	Color Wheel
4	Photography	5	Model Railroad
5	Widgets	6	Extension Cord
6	Future Category	7	Fururistic Product
7	Brand New Category

Next, let's join products (parent) to orderDetails (child), showing products with no orderDetails as NULL for order date:

select
 products.productID
, products.name
, products.price
, products.categoryID
, orderDetails.orderID
, orderDetails.quantity
, orderDetails.order_details_date 
, products.product_image
from products 
left outer join orderDetails 
on orderDetails.productID = products.productID

Orders by Product

Product ID	Product Name	Product Price	Category ID	Order ID	Quantity	Order Date	Product Image
1	E-Book clip art	100.23	1	1	1	2024-03-02 08:22:55
1	E-Book clip art	100.23	1	5	367	2024-03-03 11:10:54
2	Milk	10.32	3	5	744	2024-03-03 11:10:54
3	Light Bulb, old fashioned incandescent (tungsten filament), generates lots of heat as well as light!	55.77	3	5	4	2024-03-03 11:12:13
4	Color Wheel	55.77	3	5	8	2024-03-03 11:12:13
5	Model Railroad	22.34	4	4	3	2024-03-02 14:32:39
5	Model Railroad	22.34	4	5	43	2024-03-02 11:13:42
6	Extension Cord	22.34	5	5	77	2024-03-16 12:15:30
7	Fururistic Product	236.01	6

Next, let's join orders (parent) to orderDetails (child), showing orders with no orderDetails as NULL for Product ID, Quantity and Order Details Date:

select
 orders.orderID
, orders.customerID
, orders.orderDate
, orderDetails.productID
, orderDetails.quantity
, orderDetails.order_details_date 
from
 orders 
left outer join
 orderDetails 
on orderDetails.orderID = orders.orderID

Order Details by Order