Self Referencing Tables

Here is a database design pattern you don't see very often, but it does crop up once in a while. The self referencing table has a field that references other records within itself. In this case, we have a table named users that has the following fields:

id, first_name, last_name, manager, ismanager

The manager field is of type INT and references the id field also of type INT.

In order to make this work, we need to conceptualize two separate tables. There is truly only one table named users, but we will create a Join and provide users as a child table and give it an alias name manager. If you look under the Java tab, you will see how we accomplished this task. Note also that we treat the Join and its child table as though it were a lookup table - READ ONLY.

We add onto the JSON string the results of performing a lookup on the database for all those individuals who are flagged as being a manager.

Under the Javascript tab you'll notice we used the render property to include a function that concatenates both the first and last name for each manager to be displayed in DataTables.

Three individuals display as having no manager, that's because they are managers.

@see Join Tutorial 3 - Self Referencing Table for more information

Live example

Loading data from server