Introduction
In this tip, I want to explain how to create an unlimited category list table in database and how to read category list.
One of hardest work in creating a store is to create a category and distinguish products with sub category and maybe that sub category has inside more sub category. Some programmers create one table for each category and this forces them to have limited categories.
Background
After designing many applications, I found out that many people have problems with database designing and they create many unusable things or limited usage tables and fields. Now, I want to explain another view vision of database designing.
Let me show you my implementation for doing that.
Using the Code
In here, we have 4 fields, two of them for category names and two others for keying.
The CategoryID
is our primary key and PrevCategoryID
is a foreign key with CategoryID
.
In this implementation, we create a loop in category
table with PrevCategoryID
field and CategoryID
.
Each category
has a mother category and mother category CategoryIDs
saved in PrevCategoryID
.
The PrevCategoryID
has last CategoryID
, this means current category
is child of previous categoryID
. You can see an example of saved data in table.
Main CategoryID
is 5
and PrevCategoryID
is 0
, the zero means this category is main category. The other child of main (game
, Films
and programs
) in the PrevCategoryID
has their mother CategoryID 5
, in continuation with the game category with CategoryID 1
has one child with CategoryID 3
and PrevCategoryID 1
.
With this implementation, we have unlimited categories. Now how we can dig inside the table for category
and their child and child of child and... .
How It Works
For digging inside table in the first step, we need one partial class:
public partial class CategoryList
{
public int CategoryID { get; set; }
public CategoryList[] category { get; set; }
public string NameEn { get; set; }
public string NameFa { get; set; }
}
In this partial class, we create two fields for Category Names and two fields for keying like Category Table but with one difference, here I used recursive variable CategoryList
array type inside CategoryList
partial class instead of PrevCategoryID
for saving child category inside that.
Now main recursive function:
private static CategoryList CatLoader(int CategoryID)
{
DB_DigitalBamEntities db = new DB_DigitalBamEntities();
CategoryList categoryList = new CategoryList();
List<Tbl_Category> Child = new List<Tbl_Category>();
Child.AddRange(db.Tbl_Category.Where(tbl => tbl.PrevCategoryID == ID));
Tbl_Category main = db.Tbl_Category.Where(tbl => tbl.CategoryID == ID).Single();
categoryList.NameEn = main.NameEN;
categoryList.NameFa = main.NameFA;
categoryList.CategoryID = main.CategoryID;
if (Child.Count() == 0)
{
return categoryList;
}
CategoryList[] categoryMain = new CategoryList[Child.Count()];
for (int i = 0; i < Child.Count(); i++)
{
categoryMain[i] = CatLoader(Child[i].CategoryID);
}
categoryList.category = categoryMain;
return categoryList;
}
This function gets CategoryID
for digging inside category to find its child and finally output returns CategoryList
partial class.
Finally, we can convert CategoryList
to json with Newtonsoft library
CategoryList categorylist = CatLoader(db.Tbl_Category.Where_
(tbl => tbl.NameEN == "Main").Single().CategoryID);
JObject obj = JObject.FromObject(categorylist);
string Json = obj.ToString(Formatting.None);
Now you can use your category list Json string
anywhere.
{"NameEn":"Main","NameFa":"دسته اصلی","category":[{"NameEn":"game","NameFa":"بازی","category":_
[{"NameEn":"Strategic","NameFa":"استراتژیک","category":null,"CategoryID":3}],"CategoryID":1},_
{"NameEn":"Film","NameFa":"فیلم","category":[{"NameEn":"Action","NameFa":"اکشن","category":null,_
"CategoryID":4}],"CategoryID":2},{"NameEn":"Programs","NameFa":"پروگرم",_
"category":[{"NameEn":"Multimedia","NameFa":"چندرسانه ای","category":null,"CategoryID":7},_
{"NameEn":"Burner","NameFa":"رایت CD","category":null,"CategoryID":8},_
{"NameEn":"Antivirous","NameFa":"ویروس کش","category":null,"CategoryID":9}],"CategoryID":6}],_
"CategoryID":5}
For testing Json string, you can parse Json string in this site.
Points of Interest
- Unlimited category and sub category
- Design with one table
- Make easy digging inside category
History
- 3rd October, 2015 - Initial release
Thank you
Thanks for reading and I hope you like the control.
If you make any modifications/bug fixes/enhancements to this control, please post in the comments section with your source snippets and/or ideas.