What database structure that I should use for the site has an infinite subcategory?

Question

For example, "Dole Banana" is a kind of product that is listed in the "Bananas" category, when I open the "Fruits" category, I want to see "Dole Banana".

+ Food
|--+ Fruits
|------+ Bananas   
|------+ Apples
|--+ Vegetables
|------+ Onion
|------+ Spinach

Answer 1

, . parentId, node. / , , , , / node. , , .

: , , / .

Answer 2

-, , " " .

, , . , "" .

:

 ID  | PARENT | NAME
-----+--------+-------------
  1  |  null  | Food
  2  |   1    | Fruits
  3  |   2    | Bananas
  4  |   2    | Apples
  5  |   1    | Vegetables
  6  |   5    | Onion
  7  |   5    | Spinach

Answer 3

"" 3 .

• CategoryId null ( )
• ParentCategoryId null
• CategoryName not null

select * from Categories where ParentCategoryId is null

:

select * from Categories where ParentCategoryId = 12

Answer 4

parent_category_id / .

Answer 5

, , . : StuffID, StuffName, StuffParentID (FK to Stuff ID)

: parent-child-grandchild

Answer 6

    CREATE TABLE [dbo].[Category](
    [CategoryId] [int] NOT NULL,
    [ParentCategoryId] [int] NULL,
    [CategoryName] [nvarchar](50) NOT NULL,
     CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
    (
        [CategoryId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON 

[PRIMARY]

GO

ALTER TABLE [dbo].[Category]  WITH CHECK ADD  CONSTRAINT [FK_Category_Category] FOREIGN KEY([ParentCategoryId])
REFERENCES [dbo].[Category] ([CategoryId])
GO

ALTER TABLE [dbo].[Category] CHECK CONSTRAINT [FK_Category_Category]
GO

Answer 7

Answer 8

, . , PARENT_ID lft/rght, ( ).

Dole . _ .

. , join_products , . , Dole , . , dole . dole.

Using this structure, returning all elements from any category is easy and fast, with only one request. You cannot do this in the PARENT_ID approach (unless you are hard-coded for parents, grandchildren, etc.) Adding a category is easy. Product categorization requires the insertion of several rows in the connection table. Deleting and moving categories is a bit more complicated.