Storage and maintenance of tree structure data in basic database tables

Related discussion links:
Long ago I want to briefly talk about: storage and maintenance of tree structure data http://expert.csdn.net/Expert/TopicView1.asp? Id = 1677669

The storage of tree structure data is as follows:
Tree (ID, parentid, remark)
If it is only for storage, it is undoubtedly the most economical!
However, this structure is used to provide some application representations for queries based on slightly complex points.
Efficiency should be said to be quite low!
For example, query the path of a node!
For efficient query, we can make some effort to maintain data!
Let's take the implementation of a tree structure Forum as an example:

Tree (ID, parentid, rootid, orderid, maxid, indent, title, content, remark)
ID: integer post ID
Parentid: integer parent ID
Rootid: integer root post ID
Orderid: the ID of the post sequence in the same root post as integer.
Maxid: integer is used to place a new sticker on the top.
Indent: integer indentation
Title: varchar post title
Content: varchar post content
Remark: Details of varchar except ID and parentid

This design improves query display efficiency as long as every field is maintained!
See the following maintenance Program :
-- ===================================================== ===
Alter procedure appsp_addnew
@ ID integer
, @ Title varchar (8000) = NULL
, @ Content varchar (8000) = NULL
As
-- Declare @ ID int
-- Set @ ID = 0
If @ ID = 0
Begin
Insert into tree (parentid, orderid, indent, title, content)
Values (0, 0, 0, @ title, @ content)
-- Pin the post to the top:
Update tree
Set rootid = ID
, Maxid = (select max (ID) from tree)
Where rootid is null
End
Else
Begin
-- Adjust the internal order of the posts in the same "root Post:
Update tree
Set orderid = orderid + 1
Where rootid = (select rootid
From Tree
Where id = @ ID)
And orderid> (select orderid
From Tree
Where id = @ ID
)
-- Insert a reply post and maintain the following parameters: rootid, parentid, orderid, indent, remark, title, and content.
Insert into tree (rootid, parentid, orderid, indent, remark, title, content)
Select rootid, @ ID, orderid + 1, indent + 1
, Case when remark is null then cast (parentid as varchar)
Else remark + '-' + Cast (parentid as varchar)
End
, Isnull (@ title,'re: '+ title), @ content
From Tree
Where id = @ ID
-- Pin the post to the top:
Update tree
Set maxid = (select max (ID)
From Tree
)
Where rootid = (select rootid
From Tree
Where id = @ ID
)
End
-- ===================================================== =

This program is used
1. Add new stickers:
Appsp_addnew 0, 'First question', 'is the Earth circular? '
2. Reply to the post:
Appsp_addnew 1,'re: First question ', 'Earth is round! '

In this way, you only need to perform a simple query:
Select *, remark + '-' + Cast (parentid as varchar) + '-' + Cast (ID as varchar), Space (indent) + '['
From Tree
Order by maxid DESC, orderid
The post list, clues, and levels can be efficiently implemented!
Some work is added during maintenance!

-- Related DDL scripts:
Create Table [tree] (
[ID] [int] identity (1, 1) not null,
[Parentid] [int] Null,
[Rootid] [int] Null,
[Orderid] [int] Null,
[Maxid] [int] Null,
[Indent] [int] Null,
[Title] [varchar] (50 ),
[Content] [varchar] (200 ),
[Remark] [varchar] (250 ),
Constraint [pk_tree] primary key clustered
(
[ID]

Related discussion links:
Long ago I want to briefly talk about: storage and maintenance of tree structure data http://expert.csdn.net/Expert/TopicView1.asp? Id = 1677669

The storage of tree structure data is as follows:
Tree (ID, parentid, remark)
If it is only for storage, it is undoubtedly the most economical!
However, this structure is used to provide some application representations for queries based on slightly complex points.
Efficiency should be said to be quite low!
For example, query the path of a node!
For efficient query, we can make some effort to maintain data!
Let's take the implementation of a tree structure Forum as an example:

Tree (ID, parentid, rootid, orderid, maxid, indent, title, content, remark)
ID: integer post ID
Parentid: integer parent ID
Rootid: integer root post ID
Orderid: the ID of the post sequence in the same root post as integer.
Maxid: integer is used to place a new sticker on the top.
Indent: integer indentation
Title: varchar post title
Content: varchar post content
Remark: Details of varchar except ID and parentid

This design improves query display efficiency as long as every field is maintained!
See the following maintenance program:
-- ===================================================== ===
Alter procedure appsp_addnew
@ ID integer
, @ Title varchar (8000) = NULL
, @ Content varchar (8000) = NULL
As
-- Declare @ ID int
-- Set @ ID = 0
If @ ID = 0
Begin
Insert into tree (parentid, orderid, indent, title, content)
Values (0, 0, 0, @ title, @ content)
-- Pin the post to the top:
Update tree
Set rootid = ID
, Maxid = (select max (ID) from tree)
Where rootid is null
End
Else
Begin
-- Adjust the internal order of the posts in the same "root Post:
Update tree
Set orderid = orderid + 1
Where rootid = (select rootid
From Tree
Where id = @ ID)
And orderid> (select orderid
From Tree
Where id = @ ID
)
-- Insert a reply post and maintain the following parameters: rootid, parentid, orderid, indent, remark, title, and content.
Insert into tree (rootid, parentid, orderid, indent, remark, title, content)
Select rootid, @ ID, orderid + 1, indent + 1
, Case when remark is null then cast (parentid as varchar)
Else remark + '-' + Cast (parentid as varchar)
End
, Isnull (@ title,'re: '+ title), @ content
From Tree
Where id = @ ID
-- Pin the post to the top:
Update tree
Set maxid = (select max (ID)
From Tree
)
Where rootid = (select rootid
From Tree
Where id = @ ID
)
End
-- ===================================================== =

This program is used
1. Add new stickers:
Appsp_addnew 0, 'First question', 'is the Earth circular? '
2. Reply to the post:
Appsp_addnew 1,'re: First question ', 'Earth is round! '

In this way, you only need to perform a simple query:
Select *, remark + '-' + Cast (parentid as varchar) + '-' + Cast (ID as varchar), Space (indent) + '['
From Tree
Order by maxid DESC, orderid
The post list, clues, and levels can be efficiently implemented!
Some work is added during maintenance!

-- Related DDL scripts:
Create Table [tree] (
[ID] [int] identity (1, 1) not null,
[Parentid] [int] Null,
[Rootid] [int] Null,
[Orderid] [int] Null,
[Maxid] [int] Null,
[Indent] [int] Null,
[Title] [varchar] (50 ),
[Content] [varchar] (200 ),
[Remark] [varchar] (250 ),
Constraint [pk_tree] primary key clustered
(
[ID]
) On [primary]
) On [primary]

) On [primary]
) On [primary]