When you are starting to put together a business layer from a physical data layer that has tables with self-referential integrity, you may find aliasing the table helpful in laying out the clean data model. The example below uses an EMPLOYEE to MANAGER relationship on the EMPLOYEE table where the MANAGER_ID references back to the EMPLOYEE_ID on the same EMPLOYEE table. Using the alias feature helps separate these self-referential relationships into their own tables and allows ease of use on the presentation layer.
We will be working with tables from the HR Schema (In the Oracle APEX XE Database) to solve this problem.
The Alias we will create will be using the EMPLOYEES table. Remember, we can do this because the EMPLOYEES table stores data on two different employee types, employees and managers.
STEP 1: Creating the Alias
1) Right click the table you wish to make an Alias of, in this case it will be the EMPLOYEES table. After the right click, navigate to the New Object option and select Alias.
2) You will be given a prompt to create a new table. You will need to fill out the name in the General tab, and assign its primary key in the Keys Tab. For this example, select EMPLOYEE_ID as the primary key:
3) Hit OK and you should instantly see your new table.
STEP 2: Joining your new Alias via Foreign Keys
1) Now that we have the alias created, we need to join it with another table. We will be adding a foreign key relationship to the EMPLOYEES table, connecting it to the new alias.
3) The Physical Foreign Key window should appear, let’s rename the Foreign Key to FK_EmpToMan to represent the new Alias relationship to the parent table.
5) A Browse window should appear, you will want to go to the Name: section and input the name of the Alias you created above. Hit the button to search for the table. The Alias should appear in the window below, double-click on it.
6) Now to create the relationship between the two tables. Select the EMPLOYEE_ID from the Alias table, then select the MANAGER_ID from the EMPLOYEES table. Make sure your Expression window looks like the following before hitting OK:
7) Now let’s make sure the connection was created. CTRL-Click on both EMPLOYEES and EMP_MANAGERS then right-click anywhere within the physical layer. You should see a Physical Diagram section, go there and choose Selected Diagrams Only. This will bring up a visual diagram showing you the connections between the selected tables. You should see the following:
8) Your Alias is now ready to use for analyses. You should now be able to get some basic information solely about employees and the managers they work for, or vice versa. If you wish to find a manager’s job title or the department they work in, you will have to create an additional alias table for each and connect them to the employee alias. This will allow you to get manager specific results and employee specific results in the same analysis if you wish.
In the example above, I created an additional alias for the table JOBS which can directly relate to the MANAGERS alias.