I am trying to develop an ecommerce solution but stuck on the categories management issue and don't know how to proceed further.
See, A category will have a parent category, which in-return can also point to another parent and so on, until the root category is reached. I tried to design the following database schema,
Please tell me if that's right ?
OR Is there any tutorial on categories and parent categories that can help me understand this scenario better ?
Classic DB answer: "it depends"
A question:
If yes, your current table is OK
If no, then have a separate self referencing CategoryParent table. Your current category becomes a child of that.
This constrains your items a lowest level category, but allows a hierarchy of categories.
Your table design for handling hierarchical categories is correct. This is a general purpose design which can handle situations where there is no way to predict exactly how deep the tree will be along any one branch.
What you might want to consider in your design is whether you are going to make any amendments to assist you with the practicalities of navigating hierarchical data in a relational DBMS.
In my answer to this question I discussed the category hierarchy design.
In my answer to this other question I provide some detailed explanation around a practical implementation that makes hierarchical data much easier to work with - at least on the reading side - using concepts known as nested sets and visitation numbers.
Assuming that the column CategoryParent is the Foreign Key referring to category_id, and assuming that the CategoryParent column ALLOWS NULL, you are OK.
It is a good to append Foreign Key columns with _fk so that it is clear. Also make sure that the CategoryParent has the exact datatyp as the primary key.
Blockquote Is there any tutorial on categories and parent categories that can help me understand this scenario better ? Blockquote
Your case is a typical self-referencing or hierarchical relationship. This case exists in many cases other than categories, for example, in organization charts.
A similar example can be found here: DataModeling
