Everyone will encounter such problems in many places, such as: in one organization, from the high-level leaders to the middle leaders to ordinary employees, there is a grading relationship, these relationships are in the relational database, there is a special method . Some people build them in different tables, establish a corresponding relationship, which is a solution, but if it is in the application, it will bring more inconvenience to the software, especially the robust method, if this time Need to add a position, this solution requires more than one table, then the changes to the application will be imagined. The method I offer here is not a unique way, but a way to know, but in our forum, someone will ask and I will answer again. So I collected these questions on the one hand, plus a comprehensive explanation, organized into such an article, I hope to help everyone. If you have incorrect, please ask you. Question 1: I want to design a database model of organizational structure management, allowing users to freely define organizational structure hierarchies and relationships, do you have any good comments and classic structural models? Solve: Actually a level of model is a problem in relational database itself. The fundamental problem is that the first paradigm of the relational model requires the attribute to be divided into non-division, so that the complexity of the hierarchical implementation is directly achieved. If a property can contain multiple contents, you can have such an attribute called "lower level" properties, which contains all the subordinates of this member, but this is not desirable. Although this, the problem of organizational structure is relatively easy to solve. The reason is that the structure of the organizational structure itself is relatively simple. When designing, just make a property pointing to the primary key, that is, the foreign key of this property is the primary key of this table, it is very simple when the query is, as long as it is a self-connection, it is SELECT B. * From (t1) AS A, (T1) AS B WHERE A.P1 = B.P2 and A.p1 = "BILL" T1 is the table, P1 is the primary key, P2 is subordinates, pointing to its superior P1. The above statement queries is the list of Bill. Select a. * from (t1) as a, (t1) as b where a.p1 = b.p2 and b.p2 = "BILL" This statement query is the superior of Bill, which enables multiple layers. Question 2: How to represent such a data in the database, I hope to add some people to the blacklist in the forum, description: This database saves users who are annoying each user, for example: Mary Tom Johnsam Joe said Mary I hate Tom and John, SAM annoying Joe Solved: This is a self-connected multi-relational database in relational algebra, and the ER map is inconvenient, here I only explain the solution.
Of course, there is a registered user table, such as: CREATE TABLE [TB_user] ([userid] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL, [username] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT [PK_TB_user] PRIMARY KEY CLUSTERED ( [userid]) on [primary]) ON [PRIMARY] Go again is the table for saving hate objects: create table [bedfriend] ([Bedid] [varcha] (20) collate chinese_prc_ci_as not null, [userid1] [varchar] (20 ) COLLATE Chinese_PRC_CI_AS NULL, [userid2] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT [PK_bedfriend] PRIMARY KEY CLUSTERED ([bedid]) ON [PRIMARY]) ON [PRIMARY] GOOK a, TB_user was stored registered user bedfrend The table is as follows: Bedfrend --------------------- Bedid Userid1 Userid21 1 22 1 53 1 84 2 35 2 46 3 6
Note that this relationship is designed if Bill hates kate, Kate does not necessarily hate Bill reason everyone knows, Kate likes Bill, Bill likes NANA Issues 3: UID [User ID], depid [All Department ID] Department Table: Depid [Department ID], PDEPID [superior department ID]
Function: UidIndepsub (DEPID INT, UID INT) - Deconed whether the UID belongs to the Depid [Department ID] or its subordinate department ID. RESULTS: Select * from news where dbo.uidindepsub (depid, uid)> 1 - can be used for judging in SELECT, where multi-layer recursive judgment is required, and the hierarchy is not.
How should this function written? Solution: Create Table [DEPART] 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]
- Add Data to DEPART INTO Depart Values (1, Null) - Top level Be sure to be Nullinsert Into Depart Values (2, 1) Insert Into Depart Values (4, 2) INSERT INTO Depart Values (5, 2) 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 intasbegin 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 1Select DBo.Billfun (3, 8) - 0 for 0 is a recursive function, the landlord looks at my procedure short, I can't use it. In fact, it is always recursive UID whether it belongs to its high N-class department, not a high level department.
