MySQL Categories and Subcategories Table Structure

Question

Hi everyone, I am having a little bit of a problem with setting up a mysql table that will hold a list of categories and subcategories. I am not to sure how to setup the table. Does it need to be 2 separate tables? 1 for the main categories and 1 for the subcategories or can it be all in 1 table? Would something like this work?

Create Table categories (
    category_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    sub_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (category_id)
)

CREATE TABLE items (
    item_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description VARCHAR(100) NOT NULL,
    PRIMARY KEY (item_id),
    FOREIGN KEY (category_id) REFERENCES categories (category_id),
    FOREIGN KEY (sub_id) REFERENCES categories (sub_id)
)

Will this work or is this completely wrong? Thanks in advance for the help!

Answer 1

It depends.

Are categories and subcategories really two different things? This means categories have no parent, while subcategories are always in a parent category and have no further subs of themselves. Then two tables is ok.

If it's like a tree though, where there are just categories, which can both be children and have children, you should use one table (Google "nested set").

(Or maybe you don't mean category/subcategory but primary category/secondary category, where the secondary category is not fixed to a certain primary category. Electronics + Cycling instead of Cycling->Speedometers. Then you could use one table if it could also be Cycling + Electronics)

Answer 2

If you are 100% sure that you'll only have two levels of categories (main and sub), you can do a few different things. None of them are your proposed solution:

CREATE TABLE categories (
    id int not null primary key,
    main varchar(64)
    sub varchar(64)
);

CREATE TABLE objects (
    id int not null primary key,
    category_id int,
    name varchar(64),
    FOREIGN KEY (category_id) REFERENCES categories (id)
);

Want all vehicles?

SELECT * 
FROM objects AS o 
INNER JOIN categories AS c ON o.category_id = c.id 
WHERE c.main = 'vehicles';

Want all roflcopters?

SELECT * 
FROM objects AS o 
INNER JOIN categories AS c ON o.category_id = c.id 
WHERE c.main = 'vehicles' and c.sub='Roflcopters';

If you want something in the "vehicle" category, but not in any of the subcategories of verhicles, just have a category record where main='vehicles' with sub NULL.

Of course, this is not particularly flexible. You're stuck with just two levels of categorization, and there's not a lot of business logic embedded in your category model. But it might be sufficient for your needs.

---

Two other good, proven, models are the adjacency list model, and the nested set model, both of which are described, with lots of nice example mysql code, over here