mysql - How can I deal with the sub category in database? -

i have csv file in following format:

1043374544±collectibles 1043374544±decorative & holiday 1043374544±decorative brand 1043374544±christopher radko 1043397455±collectibles 1043397455±decorative & holiday 1043397455±decorative brand 1043397455±enesco 1043397455±precious moments 

and number itemid , '±' delimiter, after delimiter category item belongs. , each category in bottom previous one's sub category. it's collectibles --> decorative & holiday --> decorative brand --> christopher radko in case. problem items have different numbers of categories.

so how can create table can able enquire , know items in each category or sub-category.

when item can have number of categories , category can include number of items, have commonly referred n:m relation.

the usual method solve issue adding third table "relation" primary key table of 2 others.

here example (* means column part of 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 collectible item 2           | 1             // whatchamacallit collectible item 2           | 2             // whatchamacallit holiday item 

because both colums of relation-table part of primary key, can have 1 entry each possible combination. having entry means having category assignment.