c# - 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.