How can I model this class in a database?

Question

I need a little of help. This is my design to organize several categories.

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

It would be a collection. I want to store it in a database, but I don't know how can I model it in a database table. I'm using SQL Server CE.

UPDATE:

I forgot put the objective number in the class (1.1, 1.1.1).

Answer 1

You'd have a table something like this:

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

Then, if a category has a parent, you reference the id of that other row. So the table is self-referential. Toplevel categories have a null parent_category_id.

When building tables like this you do need to be careful that you don't create a circular reference.

Answer 2

For this, you can have a table where an item can reference its parent, if any.

If the ParentId column is NULL, the category is a root one. If not, the parent is referenced.

You can then find the subcategories of a category by walking through the table and searching for items with ParentId equal to Id of the category.

Note that ParentId must be indexed for better performance, and that there must be a foreign key of ParentId to Id to ensure the validity of your data.

Storing recursively the categories:

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

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

Answer 3

I'd use a simple Recursive Relation. Each Category should have a unique ID (primary key) and an optional field specifying its parent, which would be a foreign key mapping back to the same table. Categories with a NULL parent are top-level categories.

The page I linked to also has information on how you can query this structure to find top-level or mid-level Categories.

Answer 4

Here is my recommendation. Create Three tables. I am assuming that each table has different columns

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]