Tree structure-tree structure data storage and database table design

Tree structure-tree structure data storage and database table design

Tree structure is generally used in the infinite class classification, whether you use Java,.net,php,python and other language platforms for development and application, tree-shaped structure is a very common structural design.
This paper mainly solves the tree structure data storage and database table design. You are welcome to put forward your opinion according to your application. For discussion.

Data storage of tree structure
The table structure of the tree structure is as follows:/********* object:table [dbo]. [Tree] ******/
Create TABLE [dbo]. [Tree] (
[ID] [int] IDENTITY (1, 1) not NULL,
[PID] [INT] Null
[Name] [varchar] (a) COLLATE chinese_prc_ci_as NULL
) on [PRIMARY]
Go

Create CLUSTERED INDEX [ix_tree] on [dbo]. [Tree] ([PID]) On [PRIMARY]
Go

Alter TABLE [dbo]. [Tree] With NOCHECK ADD
CONSTRAINT [Pk_tree] PRIMARY KEY nonclustered
(
[ID]
) on [PRIMARY],
CONSTRAINT [child ID cannot be equal to Parent ID] CHECK ([ID] <> [PID])
Go

Alter TABLE [dbo]. [Tree] ADD
CONSTRAINT [Fk_tree_tree] FOREIGN KEY
(
[PID]
) REFERENCES [dbo]. [Tree] (
[ID]
)
Go

tree Structure database table query

Without nesting calls, you can generate a tree directly with a single statement, write a stored procedure with a temporary table, and rewrite it as a table-valued function for your reference:

The table-valued functions of the query tree table statements are as follows:
29/********* object: User-defined function dbo.fgettreetable ******/
30Create FUNCTION dbo.fgettreetable
31 (
@ID int= NULL
33)
34RETURNS @Tab TABLE (ID int, PID int, Name varchar (), Lev int)
35AS
The BEGIN
Panax Notoginseng Declare @lev int
Set @lev =0
39
While @lev =0 or @ @ROWCount >0
The Begin
Set @Lev = @Lev +1
Insert @Tab (ID, PID, Name, Lev)
Select ID, PID, Name, @Lev from tree Where (@Lev =1 and (pid= @ID) or (@ID are null and PID is null)) or (PID in Sele CT ID from @Tab Where lev= @Lev-1))
The by ID
End
Return
End
49
50GO
51
52--actual data
53Insert Tree (PID, Name) VALUES (NULL, company)
54Insert Tree (PID, Name) VALUES (3, IT)
55Insert Tree (PID, Name) VALUES (1, Fin)
56Insert Tree (PID, Name) VALUES (5, XZ)
57Insert Tree (PID, Name) VALUES (1, HR)
58GO

You can enter all records directly by querying select * from dbo.fgettreetable (null).