With the advent of ORDA, it is easier to declutter database structures by using fewer tables. One way to do this is by consolidating redundant tables via a recursive relation.

For example, a company’s database might consist of an Employees table and Managers table. Employees and Managers might share all the same attributes (Name, Email, D.O.B., etc.), except the Employees table would also have a Manager_ID field to relate to the Managers table.



Alternatively, since managers are also just employees, the Employees table can be used for both, and a recursive relation can made linking the Manager_ID with the Employee_ID in the same table. Remember that, in this instance, since Manager_ID is the foreign key to the Employee_ID primary key, the relation should be drawn from the Manager_ID to Employee_ID (N to 1).



After creating the relation, it is important to give meaningful names to the many-to-one and one-to-many links in the inspector. Simply choose names that make sense in the context. It will be useful for easily calling the relations later in code. In this case, employees have one “manager”, and a manager has many “employees”.



Also, after modifying the structure, remember to restart the 4D database to reload the ORDA model layer (see Tech Tip: Restart 4D database to reload ORDA model layer after modifying database structure: https://kb.4d.com/assetid=78121) before beginning to code with ORDA.

Now, by simply chaining .manager to a particular employee entity, data for that employee’s manager, if any, can be accessed without having to make any additional queries to a different table. Conversely, chaining .employees will return an entity selection of all the people that the employee manages, if any. In addition, via the magic of ORDA and recursive relations, the manager of the manager’s data can be accessed by simply chaining an additional .manager.