Products To Multiple Categories SQL Query and Relationship

Ecommerce and pos projects have products tables and also order, categories , items table. Those table are related to each other by foreign key. Here in this blog we will learn how a database design is done for ecommerce website.

Create Products Table

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL
);

Create Categories Table

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Create product_category relationship table

CREATE TABLE product_category (
    product_id INT,
    category_id INT,
    PRIMARY KEY (product_id, category_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

Create Other Tables

-- Create orders table
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'completed', 'cancelled') DEFAULT 'pending',
    total DECIMAL(10, 2) NOT NULL
);

-- Create order_items table (One-to-Many relationship with orders and products)
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

Let's insert some bulk data to these tables

-- Insert data into categories table
INSERT INTO categories (name, description) VALUES
('Electronics', 'Devices and gadgets'),
('Clothing', 'Apparel and fashion items'),
('Home & Kitchen', 'Furniture and kitchen appliances');

-- Insert data into products table
INSERT INTO products (name, price, status, created_at) VALUES
('Laptop', 1200.00, 'active', NOW()),
('Smartphone', 800.00, 'active', NOW()),
('Blender', 150.00, 'inactive', NOW()),
('Shirt', 25.00, 'active', NOW());

-- Insert data into product_category table (linking products to categories)
INSERT INTO product_category (product_id, category_id) VALUES
(1, 1),  -- Laptop -> Electronics
(2, 1),  -- Smartphone -> Electronics
(3, 3),  -- Blender -> Home & Kitchen
(4, 2);  -- Shirt -> Clothing

-- Insert data into orders table
INSERT INTO orders (customer_name, total, status, order_date) VALUES
('John Doe', 2000.00, 'completed', NOW()),
('Jane Smith', 75.00, 'pending', NOW());

-- Insert data into order_items table (linking orders to products)
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
(1, 1, 1, 1200.00),  
(1, 2, 1, 800.00),  
(2, 4, 3, 25.00);  

Now its time to run some queries

1. Find all the products related to Electronics category
2. Find all the products where price is between 100 to 900 along with category name
3. Find total products for each category

Find all the products related to Electronics category

SELECT p.id, p.name, p.price, p.status, p.created_at
FROM products p
JOIN product_category pc ON p.id = pc.product_id
JOIN categories c ON pc.category_id = c.id
WHERE c.name = 'Electronics';

Output:

|id |name      |price|status|created_at         |
|---|----------|-----|------|-------------------|
|1  |Laptop    |1,200|active|2024-11-07 03:13:59|
|2  |Smartphone|800  |active|2024-11-07 03:13:59|

Find all the products where price is between 100 to 900 along with category name

SELECT p.id, p.name, p.price, p.status, p.created_at, c.name AS category_name
FROM products p
JOIN product_category pc ON p.id = pc.product_id
JOIN categories c ON pc.category_id = c.id
WHERE p.price BETWEEN 100 AND 900;

Output:

|id |name      |price|status  |created_at         |category_name |
|---|----------|-----|--------|-------------------|--------------|
|2  |Smartphone|800  |active  |2024-11-07 03:13:59|Electronics   |
|3  |Blender   |150  |inactive|2024-11-07 03:13:59|Home & Kitchen|

Find total products for each category

SELECT c.name AS category_name, COUNT(p.id) AS total_products
FROM categories c
JOIN product_category pc ON c.id = pc.category_id
JOIN products p ON pc.product_id = p.id
GROUP BY c.id;

Output:

|category_name |total_products|
|--------------|--------------|
|Clothing      |1             |
|Electronics   |2             |
|Home & Kitchen|1             |