Categories should be in a recursive table (references itself)
Main categories have NULL parent_id, subcategories reference the parent category.
Products then reference the category they belong to.
Code:
CREATE TABLE category (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
parent_id INT NOT NULL REFERENCES category(id),
name VARCHAR(255) NOT NULL,
description TEXT NULL
);
INSERT INTO category (id, parent_id, name)
VALUES
(1, NULL 'main category 1'),
(2, NULL 'main category 2'),
(3, 1 'sub category 1-1'),
(4, 1 'sub category 1-2'),
(5, 2 'sub category 2-1'),
(6, 2 'sub category 2-2');
CREATE TABLE product (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
category_id INT NOT NULL REFERENCES category(id),
name VARCHAR(255) NOT NULL,
description TEXT NULL
);
To select only categories that have products in them, use aggregation:
Code:
SELECT c.*, COUNT(p.id)
FROM category AS c
INNER JOIN product AS p ON c.id = p.category_id
GROUP BY c.id
HAVING COUNT(p.id) > 0;