MySQL 8 Example: Storing Categories and Subcategories in a Single Table

Introduction

Organizing data effectively is one of the main challenges you might face when developing a database for an application, especially when dealing with hierarchical data such as categories and subcategories. MySQL 8 offers several features to handle hierarchical data. In this tutorial, we will explore how to store categories and their respective subcategories within a single table in MySQL 8, using various techniques and functions.

Prerequisites

• A working MySQL 8 installation.
• Basic knowledge of SQL and database theory.

Designing the Table

To store categories and subcategories, we will use a self-referencing table. That means we will have a single table where each entry can refer to another entry in the same table as its parent.

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

Here, parent_id is a foreign key that references the id of another category within the same table, allowing us to define a hierarchy.

Adding Categories

Let’s add some categories to our table:

INSERT INTO categories(name, parent_id) VALUES
('Electronics', NULL),
('Computers', 1),
('Laptops', 2),
('Smartphones', 1),
('Cameras', 1);

In the above SQL, ‘Electronics’ is a top-level category with no parent (parent_id is NULL), whereas ‘Computers’, ‘Laptops’, ‘Smartphones’, and ‘Cameras’ are subcategories.

Retrieving Categories

Fetching the entire hierarchy is a common requirement:

SELECT child.name AS subcategory,
       parent.name AS category
FROM categories AS child
LEFT JOIN categories AS parent ON child.parent_id = parent.id;

Results from this query will show each subcategory alongside its parent category. Top-level categories will have a NULL category value.

Using Common Table Expressions (CTE)

MySQL 8 supports CTEs, which makes querying hierarchical data much more simple and readable:

WITH RECURSIVE category_path (id, name, path) AS (
    SELECT id, name, name as path FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, CONCAT(cp.path, ' > ', c.name)
    FROM category_path AS cp JOIN categories AS c ON cp.id = c.parent_id
) SELECT * FROM category_path;

This code will produce a list of categories showing the full path from top-level to subcategory.

Subcategory Depth Level

To showcase depth levels:

WITH RECURSIVE category_depth AS (
    SELECT id, name, 0 AS depth FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, cd.depth + 1
    FROM category_depth AS cd JOIN categories AS c ON cd.id = c.parent_id
) SELECT * FROM category_depth ORDER BY depth;

This code will not only fetch the categories but also list them with their respective depth levels.

Manipulating Category Data

Moving categories around entails adjusting the parent_id values. For instance, to make ‘Cameras’ a subcategory of ‘Computers’ instead of ‘Electronics’, you would:

UPDATE categories SET parent_id = 2 WHERE id = 5;

Deleting categories can be a bit tricky because you may need to consider what happens to subcategories. You can use a recursive delete or reassign subcategories before deletion.

Best Practices and Performance

Storing hierarchical data should also come with a consideration for best practices and performance:

• Use proper indexing.
• Consider the use of stored procedures for complex hierarchy manipulation.
• Avoid deep hierarchies for performance reasons.
• Keep the design flexible to accommodate future changes.

Conclusion

In conclusion, MySQL 8 provides powerful and flexible ways to manage hierarchical data such as categories and subcategories within a single table. By following the examples in this tutorial, developers can implement a functional category structure with ease.