Working with Self-Referencing Tables in Master-Detail Browser

This topic explains how to work with self-referencing tables in Master-Detail Browser.

Self-referencing table is a table that is a parent and a dependent in the same referential constraint. I. e. in such tables a foreign key constraint can reference columns within the same table.

The example specified below shows how to view data in a self-referencing table regarding available relations between columns using Master-Detail Browser.

Prerequisites

To perform the steps described in the Viewing Data in a Self-Referencing Table part of this topic, you will need a self-referencing table itself. Below is an example of such table (the Employees table) and it will be used in the Viewing Data in a Self-Referencing Table section as a sample table:

EmpNo EName Job MGR HireDate Sal Commission DeptNo
7369 SMITH CLERK 7902 2/17/1980 12:00:00 AM 800 (null) 20
7499 ALLEN SALESMAN 7698 02/20/1981 12:00:00 AM 1600 300 30
7521 WARD SALESMAN 7698 02/22/1981 12:00:00 AM 1250 500 30
7566 JONES MANAGER 7839 04/02/1981 12:00:00 AM 2975 (null) 20
7654 MARTIN SALESMAN 7698 09/28/1981 12:00:00 AM 1250 1400 30
7698 BLAKE MANAGER 7839 05/01/1981 12:00:00 AM 2850 (null) 30
7782 CLARK MANAGER 7839 06/09/1981 12:00:00 AM 2450 (null) 10
7788 SCOTT ANALYST 7566 07/13/1980 12:00:00 AM 3000 (null) 20
7839 KING PRESIDENT (null) 11/17/1980 12:00:00 AM 5000 (null) 10
7844 TURNER SALESMAN 7698 09/08/1981 12:00:00 AM 1500 0 30
7876 ADAMS CLERK 7788 07/13/1987 12:00:00 AM 1100 (null) 20
7900 JAMES CLERK 7698 12/03/1980 12:00:00 AM 950 (null) 30
7902 FORD ANALYST 7566 12/03/1980 12:00:00 AM 3000 (null) 20
7934 MILLER CLERK 7782 01/23/1982 12:00:00 AM 1300 (null) 10

Viewing Data in a Self-Referencing Table

Task: it is required to view all employees regarding the levels of company hierarchy using the data available in the sample Employees table.

Solution: because the manager is also an employee, there is a relationship from the MGR column to the EmpNo column. The EmpNo column contains identification number of all employees, and the MGR column contains the identification number of the manager a person subordinates to (you see, the company president has (null) in the MGR column, as he doesn’t subordinate to anyone). Now, as you’ve understood the relationship between these columns, you can perform the following steps to solve the task specified above using dbForge Studio Master-Detail Browser:

  1. Click Master-Detail Browser on the Database Tools toolbar. An empty Master-Detail Browser document will open.

  2. Drag’n’drop the Employees table you’ve created onto the designer surface.
  3. Create a relationship between the MGR and EmpNo columns by selecting the MGR column and dragging it to the EmpNo column. An arrow indicating the relationship will be displayed on the diagram:

    Self-Referencing Table

    Note
    There is no foreign key in this table, but if there is one and a relation between columns was established earlier, it’s displayed on the diagram after dropping the table onto it.

  4. Click the Browse Data button.
  5. In the Data view that opened, select the row with PRESIDENT specified in the Job column in the upper part of the view, and click + near each detail record in the lower part of the view to see the next level of company hierarchy and the employees belonging there:

    Self-Referencing Table Result