Summary of hierarchical problem in database design (for organization chart and family tree) __ Database

In many places, you will encounter such problems as:
In an organization, from high-level leadership to middle-level leadership to ordinary workers, there is a hierarchical relationship, these relationships in the relational database in the expression, there is a special way.
Some people set them up in separate tables, establish the corresponding relationship, although this is a solution, but if in the application, it will cause a lot of inconvenience to the software, especially the program robustness method, if you need to add a job, this solution will need to add a table, The changes to the application will be conceivable at this time.
The method I provide here is not a unique method, but a method of knowing, but it is often asked and answered repeatedly in our forums.
Therefore, on the one hand, I will collect these questions, together with a comprehensive explanation, organized into such an article, I hope to help.
Please give your comments if there are any incorrect points.

Question one:
Want to design an organization structure Management database model, can let the user define the organization structure level and the relation freely, everybody has the good opinion and the classical structure model.

Solve:
In fact, a level of the model in the relational database itself is a problem. The fundamental problem is that the first paradigm of the relational model requires that attributes be divided into no more points, which in this case directly results in the complexity of the hierarchical structure implementation. If an attribute can contain more than one content, it can have an attribute called "subordinate", which contains all the subordinates of this member, but this is undesirable.
In spite of this, the problem of organizational structure is relatively easy to solve. The reason is that the structure of the organization itself is relatively simple.
In the design of the time to do a point to their own primary key properties, that is, the foreign key of this property is the primary key of this table, in the query is very simple, as long as a self connection can be, that is
Select b.* from (T1) as A, (T1) as B where A.p1=b.p2 and a.p1= "Bill"
T1 for the table, p1 as the primary key, P2 for subordinates, it points to its superior P1. The above statement inquires into Bill's subordinate list.
Select A.* from (T1) as A, (T1) as B where A.p1=b.p2 and b.p2= "Bill"
This statement inquires the bill's superiors, and this achieves multiple layers.

Question two:
How to represent such data in the database, I hope in the forum, to make some people blacklist function, Description:
This database holds users who are annoying to each user, such as:
Mary Tom John
Sam Joe.
It says Mary hates Tom and John, Sam hates Joe.

Solve:
This is a self connected Many-to-many relational database in relational algebra, and ER diagrams are inconvenient to draw, where I only illustrate the solution.
Of course there is a registered user table, such as:
CREATE TABLE [Tb_user] (
[UserID] [varchar] (COLLATE) chinese_prc_ci_as not NULL,
[Username] [varchar] (a) COLLATE chinese_prc_ci_as NULL,
CONSTRAINT [Pk_tb_user] PRIMARY KEY CLUSTERED
(
[UserID]
) on [PRIMARY]
) on [PRIMARY]
Go
The second is to save the list of pesky objects:
CREATE TABLE [Bedfriend] (
[Bedid] [varchar] (COLLATE) chinese_prc_ci_as not NULL,
[USERID1] [varchar] (m) COLLATE chinese_prc_ci_as NULL,
[Userid2] [varchar] (m) COLLATE chinese_prc_ci_as NULL,
CONSTRAINT [Pk_bedfriend] PRIMARY KEY CLUSTERED
(
[Bedid]
) on [PRIMARY]
) on [PRIMARY]
Go

OK, Tb_user is a registered user.
The following information is stored in the Bedfrend table:
Bedfrend
---------------------
Bedid Userid1 Userid2
1 1 2
2 1 5
3 1 8
4 2 3
5 2 4
6 3 6

Note that the relationship is designed to mean that if Bill hates Kate, Kate doesn't necessarily hate bill.
The reason everyone knows, Kate likes bill, Bill likes Nana.


Question three:
User table: uid[user id],depid[Department ID]
Department Table: depid[Department id],pdepid[Superior Department ID]

Function: uidindepsub (depid int,uid int)--Determines whether the UID belongs to the depid[Department ID] or its subordinate department ID.
Result: SELECT * FROM news where dbo. Uidindepsub (DEPID,UID) >1--can be used in select judgments, where multiple levels of recursive judgment are required.

How should this function be written?

Solve:
CREATE TABLE [Depart] (
[Depid] [INT] Not NULL,
[Pdepid] [INT] Null
CONSTRAINT [Pk_depart] PRIMARY KEY CLUSTERED
(
[Depid]
) on [PRIMARY],
CONSTRAINT [Fk_depart_depart] FOREIGN KEY
(
[Pdepid]
) REFERENCES [Depart] (
[Depid]
)
) on [PRIMARY]

--adding data to depart
Insert into depart values (1,null)--the upper level must be NULL
Insert into depart values (2,1)
Insert into depart values (3,1)
Insert into depart values (4,2)
Insert into depart values (5,2)
Insert into depart values (6,3)
Insert into depart values (7,4)
Insert into depart values (8,4)
Insert into depart values (9,5)
Insert into depart values (10,6)

Create function Billfun (@departid int, @uid int)
returns int
As
Begin
DECLARE @temp int
SELECT @temp =pdepid
From Dbo.depart
WHERE (depid = @uid)
if (@temp = @departid)
Return 1
if (@temp is null)
return 0
Return (Dbo.billfun (@departid, @temp))
End

Select Dbo.billfun (2,8)
--For 1
Select Dbo.billfun (3,8)
--For 0

The above function is a recursive function, the landlord at that time to see my program short still think can not use it. In fact, it's always recursive. Query UID is not part of their high N level department, not the higher level department.