Unlimited Category List Table

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

• 3^rd 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.