How can I build a SQL design, which provides Sub-Sub-Categories for products?

Question

I'm trying to build a webshop-database and I have a problem, when it comes down to the design of a SQL-product table, which provides Sub Sub Categories.

For examle I have a main category, which is always set:

1.Cars

Then I have the first sub-category

1.1 BMW
1.2 Mercedes

And finally in some cases I have an even deeper category.

1.1.1 BMW M3
1.1.2 BMW M5

1.2.1 Mercedes 1
1.2.2 Mercedes 2

Currently I have three tables:

tbl_Article
int ArticleId
char Description
decimal Price
int Category
int SubCategory

tbl_Category
int CategoryId
char Description
int hasSubCategories

tbl_SubCategory
int SubCategoryId
int Category
char Description

How can I solve this problem, so that I can have multiple sub-categories if needed?

Thank you so much!

Answer 1

You can design a "category-table", with an foreign-key on itself:

tbl_category

int   id                                            --primary key
int   fk_parentCategory REFERENCES tbl_category.id  --foreign key, can be null
char  description
...

tbl_product

int   id         --primary key
int   category   --foreign key, can not be null
char  name
...

The column fk_parentCategory is an foreign-key, pointing on its own table (but a different category). It can also be null.

If null, it's a top-category. If not, its a subcategory.

You only have to care, that a category never points to itself. So the column id must never have the same value as the column fk_parentCategory.