I need some of help. This really is my design to arrange several groups.

   Category 1
     Sub Category 1.1
       Sub Category 1.1.1
     Sub Category 1.2
     Sub Category 1.3

It might be an assortment. I wish to store it inside a database, but I'm not sure how do i model it inside a database table. I am using SQL Server CE.

enter image description here


I didn't remember place the objective number within the class (1.1, 1.1.1).

You'd possess a table something similar to this:

  • category
    • id (primary key, not null)
    • title (text, not null)
    • parent_category_id (foreign answer to category.id, nullable)

Then, if your category includes a parent, you reference the id of this other row. Therefore the table is self-referential. Toplevel groups possess a null parent_category_id.

When building tables such as this you need to be cautious you don't produce a circular reference.

With this, you'll have a table where a product can reference its parent, if any.

enter image description here

When the ParentId column is NULL, the course is really a root one. Otherwise, parents is recommended.

After that you can discover the subcategories of the category when walking with the table and looking out for products with ParentId comparable to Id from the category.

Observe that ParentId should be indexed for better performance, which there has to be an overseas key of ParentId to Id to guarantee the validity of the data.

Storing recursively the groups:

private void SaveCategoryRecursively(Category category)
    foreach (var subCategory in category.SubCategories)
insert into [dbo].[Categories] ([Id], [ParentId], ...)
values (@id, @parentId, ...)", ...);

public void SaveCategories(IEnumerable<Category> rootCategories)
    foreach (var category in rootCategories)
insert into [dbo].[Categories] ([Id], [ParentId], ...)
values (@id, NULL, ...)", ...);

I'd make use of a simple Recursive Relation. Each Category must have a distinctive ID (primary key) as well as an optional area indicating its parent, which will be a foreign key mapping to exactly the same table. Groups having a NULL parent are top-level groups.

The page I associated with also offers information about how you are able to query this structure to locate top-level or mid-level Groups.

Here's my recommendation. Create Three tables. I'm presuming that every table has different posts

Category { Id, Name,...} 
SubCategory {Id, Name, ..., **ParentID**}  [ParentID is a FK from Category table Id Column] 
SubSubCategory {Id, Name, ...,  **SubParentID**}  [SubParentID is a FK from SubCategory table ParentID Column]