A summary of hierarchical layering in database design (for questions such as organizational chart and genealogy) people in many places will encounter such problems, such as:
In an organization, from senior leaders to middle-level leaders to ordinary workers, there is a hierarchical relationship, these relationships in the relational database, when expressed, there is a special method.
Some people put them in separate tables, to establish the corresponding relationship, although it is a solution, but if in the application, it will bring a lot of inconvenience to the software, especially the program robustness method, if you need to add a position at this time, this solution will need to add a table, The changes to the application at this time will be conceivable.
The approach I am providing here is not a unique approach, but a method that is known to all, but is often asked in our forums and I have repeatedly answered them.
Therefore, on the one hand, I will collect these issues, together with a comprehensive explanation, organized into such an article, I hope to be helpful to everyone.
If there is an incorrect point, please give us your opinion. Question one:
Want to design a database model of organizational structure management, allowing users to freely define organizational structure levels and relationships, do you have good opinion and classic structure model? Solve:
In fact, a hierarchical 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 non-re-divided, which directly results in the complexity of the hierarchy implementation. If a property can contain more than one content, it can have a property called "subordinate", which contains all the subordinates of this member, but this is undesirable.
Although this is the case, the problem of organizational structure is more easily solved. The reason is that the structure of the organization itself is relatively simple.
In the design of the time just to do a point to the primary key of the property, that is, the foreign key of this property is the primary key of the table, when 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 is a table, p1 is the primary key, P2 is subordinate, it points to its superior P1. The above statement queries the list of Bill's subordinates.
Select A.* from (T1) as A, (T1) as B where A.p1=b.p2 and b.p2= "Bill"
This statement queries the superior of Bill, which achieves multiple layers. Question two:
How to represent such data in the database, I hope in the forum, to make some people to blacklist the function, description:
This database holds users who are annoying to each user, for example:
Mary Tom John
Sam Joe
The above means that Mary hates Tom and John and Sam hates Joe to solve it:
This is a self-connected many-to-many relational database in the relational algebra, the ER diagram is inconvenient to draw, here I only explain 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] (Ten) COLLATE chinese_prc_ci_as NULL,
CONSTRAINT [Pk_tb_user] PRIMARY KEY CLUSTERED
(
[UserID]
) on [PRIMARY]
) on [PRIMARY]
GO
Here's the table that saves the pesky objects:
CREATE TABLE [Bedfriend] (
[Bedid] [varchar] (COLLATE) chinese_prc_ci_as not NULL,
[USERID1] [varchar] (COLLATE) Chinese_prc_ci_as NULL,
[Userid2] [varchar] (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 if Bill hates Kate, Kate doesn't necessarily hate bill.
Cause everybody knows Kate likes bill, Bill likes Nana.
Question three:
User table: uid[user id],depid[Department ID]
Department Table: depid[Department id],pdepid[parent Department ID] Function: uidindepsub (depid int,uid int)--Determine if 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 to judge in Select, where multiple levels of recursive judgment are required and the hierarchy is variable. What should I write this function? 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 (@[email protected])
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 the 0 above function is a recursive function, the landlord at that time to see my program short also thought can not use it. In fact, it is always recursive query uid is not belong to their high N-class department, rather than the high-level department.
Give you an example:
/*
1. Implement a cascading query for example, provide an employee number that can be queried for the
Information about the employee's superiors and indirect superiors.
2. Implement a cascading query for example, provide an employee number that can be queried for the
Information about subordinates and indirect subordinates of employees.
3. Implements cascading deletions. For example, specify an employee number when the employee is deleted
Basic information, all subordinates of the employee are deleted. No matter
Superiors or subordinates, including employees themselves.
4. Implement cascading updates. When an employee's work number changes, all employees
The value of the Reportto field for the employee who is the direct ancestor becomes the new work number.
*/
CREATE TABLE EMP (
Eid int primary KEY,
ename varchar (20),
Sal Money,
Reportto int references emp (EID)--the employee's immediate superior
)
--drop table emp
INSERT INTO EMP Select 1001, ' Rain ', 1000,null
Union select 1002, ' Ann ', 3000,1001
Union Select 1003, ' Lopez ', 2000,1001
Union select 1004, ' Nakata ', 3000,1002
Union Select 1005, ' Tae ', 1500,1004
Union select 1006, ' Raul ', 900,1004
Union select 1007, ' Owen ', 15000,1006
--select * from EMP
/* Implement a cascading query for example, provide an employee number that can be queried for information about the employee's superiors and indirect superiors. */
create proc p1 @eid int
As
Begin
Declare @re table (Eid int,level int)
DECLARE @l int
Set @l=0
Insert @re Select @eid, @l
While @ @rowcount >0
--Global variable @ @rowcount, record the number of rows affected by the last operation
Begin
Set @[email protected]+1
Insert @re Select A.reportto,@l from emp as a, @re as B where A.eid=b.eid and[email protected]
--Loop inserts the direct ancestor of the current record into the @re
End
Select A.* from @re as b inner join EMP as a on a.eid = B.eid
End
--drop proc P1
EXEC P1 1005
/* Implement cascading queries For example, provide an employee number that can be queried for information about the employee's subordinate and indirect subordinates. */
create proc P2 @eid int
As
Begin
Declare @re table (Eid int,level int)
DECLARE @l int
Set @l=0
Insert @re Select @eid, @l
While @ @rowcount >0
--Global variable @ @rowcount, record the number of rows affected by the last operation
Begin
Set @[email protected]+1
Insert @re Select A.eid,@l from emp as a, @re as B where A.reportto=b.eid and[email protected]
--Loop inserts the immediate subordinate of the current record into the @re
End
Select A.* from @re as b inner join EMP as a on a.eid = B.eid
End
--drop proc P2
EXEC P2 1004
Implementing a cascading query
