Tree Structure-data storage and database table design with Tree Structure

Tree Structure-data storage and database table design with Tree Structure

Tree structures are generally used for unlimited classification. Whether you use Java,. net, PHP, Python, or other language platforms for development and application, tree structures are one of the most commonly used structural designs.
This article focuses on data storage and database table design with a tree structure. You are welcome to give your opinions based on your application! For discussion!

Data storage in a 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] (10) 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 [subid 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 nested calls, you can directly use a statement to generate a tree, write a stored procedure in a temporary table, and rewrite it into a table value function for your reference:

The table Value Function of the query tree TABLE statement is as follows:

29/********* object: User-Defined Function DBO. fgettreetable ******/
30 create function dbo. fgettreetable
31 (
32 @ ID Int = NULL
33)
34 returns @ tab table (ID int, PID int, name varchar (10), levint)
35as
36 begin
37 declare @ levint
38 set @ lev= 0
39
40 while @ lev= 0 or @ rowcount> 0
41 begin
42 set @ maid + 1
43 insert @ tab (ID, PID, name, column)
44 select ID, PID, name, @ levfrom tree where (@ lev= 1 and (pid = @ ID) or (@ ID is null and PID is null ))) or (PID in (select ID from @ tab where lev= @ Lev-1 ))
45 order by ID
46 end
47 return
48 end
49
50go
51
52 -- actual data
53 insert tree (PID, name) values (null, company)
54 insert tree (PID, name) values (3, it)
55 insert tree (PID, name) values (1, fin)
56 insert tree (PID, name) values (5, xz)
57 insert tree (PID, name) values (1, HR)
58go

You can directly query select * From DBO. fgettreetable (null) to enter all records.