How can I deal with the sub category in database?

Question

I have this csv file in the following format:

1043374544±Collectibles
1043374544±Decorative & Holiday
1043374544±Decorative by Brand
1043374544±Christopher Radko
1043397455±Collectibles
1043397455±Decorative & Holiday
1043397455±Decorative by Brand
1043397455±Enesco
1043397455±Precious Moments

and the number is the itemID and '±' is the delimiter, after the delimiter is the category where the item belongs. And each category in the bottom is the previous one's sub category. So it's like Collectibles --> Decorative & Holiday --> Decorative by Brand --> Christopher Radko in this case. The problem is items will have different numbers of categories.

So how can I create table that I can be able to enquire and know what items are in each category or sub-category.

Sorry but the 1043374544±Collectibles should be on each line — ZHICHU ZHENG, Feb 05 '16 at 15:38
Please don't start all your sentences with "So" - it makes your question more likely to get ignored. — Pete, Feb 05 '16 at 15:45
You seen to go for some Tree DB; http://stackoverflow.com/questions/2378678/mysql-recursing-a-tree-structure may help you — Blag, Feb 05 '16 at 15:46

score 1 · Answer 1 · answered Feb 05 '16 at 15:54

When an item can have any number of categories and a category can include any number of items, then you have what is commonly referred to as a n:m relation.

The usual method to solve this issue is by adding a third table "relation" where the primary key is the table of the two others.

Here is an Example (* means column which is part of the primary key)

Table products

id* | name            | price
----+-----------------+--------
1   | Thingamy        | 3.45
2   | Whatchamacallit | 2.99
3   | FooBarWidget    | 1.00

Table categories:

id* | name
----+-------------
1   | Collectibles
2   | Holiday
3   | Decorative

Table category_product

product_id* | category_id*
------------+---------------
1           | 1             // Thingamy is a Collectible item
2           | 1             // Whatchamacallit is a Collectible item
2           | 2             // Whatchamacallit is also a Holiday item

Because both colums of the relation-table are part of the primary key, it can have exactly one entry for each possible combination. Having the entry means having that category assignment.

Is there a way to know which category is the sub-category and which one is the parent one? — ZHICHU ZHENG, Feb 05 '16 at 16:01

How can I deal with the sub category in database?

1 Answers1