26
Views
5
Comments
Recursive Entities Best practice
Discussion

Suppose we have an employee table. An employee reports to a certain manager. Every manager is also an employee. This is an example of recursive database tables. What is the best practice in Outsystems when you have recursive entities? Are both solutions equal?

or

Hi Danny,

The best option for your problem is the first option, one entity with a column to the same table.

The option 2 works, but typically is used when an employee reports to more than one person, for example.

Thank you.


Hi Danny,

For your case, the first option seems to be the best. The second option can be used when an employee reports to more than one person (as Gonçalo stated) or when there are specific attributes for the reporting process.


Cheers,

António Pereira

Rank: #129

Hi Danny,

I agree with both that for this simple example, the main difference is that the top solution only allows employees to report to one manager, and that you can't add attributes to the relationship.

In realistic applications, I would think that you would quickly find the first overly simplistic as  

  • the reporting structure can change over time, and you want to capture that information (for example by adding start and end dates in the relation table)
    • employees change departments
    • employees get promotions
    • managers take on other departments
  • employees might have other relationships among each other than just manager/managed (such as they might have an operational manager as well as an hr manager), your employee table can quickly get cluttered with lots of foreign keys to details that are not really related to them personally
  • I could imagine that the department/users allowed to update the home address of an employee are not the same ones and are not using the same screens/actions as the users allowed to change the reporting structure, so having it modelled separately in your data supports a more natural/simple application design

Dorine