A crm system data design problem

A crm system data design problem crm database optimization

For example, there is an employee table with employee ID, name, phone number, address, department, and other information. If the department is not the headquarters, you need to continue reporting.

Now we need to design the database:
1. select an employee and write the report chain of the employee.
2. if a department manager is used, all employees in the department are listed.


The idea I came up with is recursive, but it may not work very efficiently considering the large amount of data.

Is there any good optimization method. If recursion is used, provide some examples and solutions. Thank you.

Reply to discussion (solution)

I wonder if you want to ask about database design or implementation.

I wonder if you want to ask about database design or implementation.

Want to ask about database design

What bureaucracy? How many recursion attempts are you going to perform?

Recursively write all your departments to the array storage file.

Depid in employee table,
Department table:
Id pid title

What bureaucracy? How many recursion attempts are you going to perform?

Is there a recursive solution?

It is mainly because the recursion is too large to be efficient. Is there any other way to solve it?

Depid in employee table,
Department table:
Id pid title

Can it be detailed?

Add a department manager to the department table

Department code
01
0101
010101

The moderator method is feasible, and the database does not need to be designed. after the department code is properly designed, the report chain can be directly separated.

Department code
01
0101
010101

So if we want to design it at the database level, can we only use recursion?

A table is differentiated by parentID.

In fact, I want to take a detailed step,
It is better to have a detailed example. Great Gods