Foriegn key for 3rd level of sub-category, when comparatively smaller than level 2 (1 Viewer)

[Ihk]

I came to situation, where I had to ask to increase my knowlege. I this is very basic question, but I am learning.
Database is about articles, suppliers etc. It has three levels of categories.
CategoryMain, SubCatLevel1, Subcatlevel2.
Not all the subcategories of level1 are further subcategorized to level2. So basically the last sub category (over all 3rd level) is shrinked.
And I know the last level (subcategorylevel2_ID) will serve as foriegn key in article detail table.
What about those articles who have level1 but dont have level2 of subcategory?
What is the best solution in this case?
Example below
1) CategoryMain

ID_CategoryMain	CategoryMain
1	General
2	Groceries

2nd category also decides about tax percentage (19%, 7%, 0%)

2) SubcategoryLevel1

ID_Subcatlevel1	ID_CategoryMain	Subcatlevel1	ID_TaxRate
1	2	Fruits-Vegetables	2
2	2	Rice	2
3	2	Flour	2
4	2	Spices	2
5	2	Drinks	1
6	1	All	1
7	1	Men	1
8	1	Women	1
9	1	Kids	1

3) SubcategoryLevel2

ID_CatSubLevel2	ID_Subcatlevel1	Subcatlevel2
1	8	Clothes
2	8	Jewelry
3	8	Shoes
4	9	Bikes
5	9	Clothes
6	9	Toys

For better understanding, what I mean is - If I combine the text of all, it look like as below
CategoryQ

CategoryMain	Cat level 1	Cat level 2
General	All
General	Men
General	Women	Clothes
General	Women	Jewelry
General	Women	Shoes
General	Kids	Bikes
General	Kids	Clothes
General	Kids	Toys
Groceries	Fruits-Vegetables
Groceries	Rice
Groceries	Flour
Groceries	Spices
Groceries	Drinks

thank you

 

[plog]

It really depends on your aim. If the goal is just the query results that you posted at the end, then you can accomplish that with a LEFT JOIN. If you have further data that needs to relate to a subcategory2 record then you probably need dummy records for all subcategory1 records in the subcategory2 table. We'd really need a bigger picture of this database and how you plan on using all that category/subcategory data.

For the query, you simply need a LEFT JOIN:

SELECT CategoryMain, Subcatlevel1, Subcatlevel2
FROM CategoryMain
LEFT JOIN SubcategoryLevel1 ON CategoryMain.ID_CategoryMain = SubcategoryLevel1.ID_CategoryMain
LEFT JOIN SubcategoryLevel2 ON SubcategoryLevel1.ID_Subcatlevel1 = SubcategoryLevel2.ID_Subcatlevel1;

 

[Ihk]

It really depends on your aim. If the goal is just the query results that you posted at the end, then you can accomplish that with a LEFT JOIN. If you have further data that needs to relate to a subcategory2 record then you probably need dummy records for all subcategory1 records in the subcategory2 table. We'd really need a bigger picture of this database and how you plan on using all that category/subcategory data.

For the query, you simply need a LEFT JOIN:

SELECT CategoryMain, Subcatlevel1, Subcatlevel2
FROM CategoryMain
LEFT JOIN SubcategoryLevel1 ON CategoryMain.ID_CategoryMain = SubcategoryLevel1.ID_CategoryMain
LEFT JOIN SubcategoryLevel2 ON SubcategoryLevel1.ID_Subcatlevel1 = SubcategoryLevel2.ID_Subcatlevel1;

Thank you for your comment.
It is just start, creating backend at 1st. Yes plan is bigger, I want to really corelate records.
So later multiple business reports can be generated based on last level of sub category. Article based progress and so on.
Furthermore, 2nd level here is categorizing into sales tax rates, which are different for basic needs and others.
Coming back to dummy records, I had that in mind. But how will it be possible, if user is creating categories and subcategories of his choice. And he does not know anything about database, if i ask to must create all categories. (If they forget).

I am thinking, to automatically insert the same text from level 2 to level, when user does not have will to create. This way table will its records, at least equal to level 2.
For example above rice and Flour.
But how to automate, if user mistakenly wont.

 

[jdraw]

My suggestion is that you provide the 30,000 ft overview of what you are trying to support with a database. Include the proposed scope so readers can offer meaningful and focused responses. You mention suppliers; do you also have Customers? Is this a piece of an Inventory/Stock Management system?
I think you need more examples for people to provide more info on your categories. Fruits-Vegetables doesn't seem to provide a very distinct category at "level1".
See this "shopping list".
How many levels do you anticipate?
Have you tried to build your subcategories based on your "business needs"?
Are you working with or have you reviewed any standardized categorization? eg FAO/WHO?