Environment (JDeveloper 22.214.171.124.0, ADF BC, hr schema)
Sometimes you may have a table with more than one type of rows. For example, a company may have a distinction between salaried employees, and commissioned employees, but we store all our employees in the employees table rather than create new table for each type of employees. In this post I will explain how to use discriminator columns and extended-entities (sub-entities) to represent a table with two different kinds of rows, each with its own business logic.
This example based on EMPLOYEES table in hr schema with a small modification, one extra column (em_type) is added to the EMPLOYEES table to distinguish between different kinds of employees. You can use this SQL statement to add the column.
ALTER TABLE EMPLOYEES ADD emp_type varchar2(1);
A discriminator column is used to indicate which attribute’s value distinguishes the type of row, You can create different entity objects for each of these types, each one implements its own business logic. To represents a table with a discriminator column, use the following steps:
- Create an entity object based on EMPLOYEES table in hr schema.
- Double click on youe entity object to open it in the main window, click on the Attributes node, select the EmpType attribute and click edit.
- Check the discriminator check box as shown below, then click OK.
- In your Application Navigator area, right click on Employees entity, and select New Extended Object from the menu as shown below.
- Select your package and enter SalariedEmployee in the Name field, and click OK button.The new Entity Object will be open in the main window.
- Select the Attributes node, then select the EmpType attribute and any other attribute you want to override it and click the Override button as shown below.
- After you click on the button, the Edit (pencil symbol) becomes enabled for the overridden attribute. keep EmpType attribute selected and click the edit symbol.
- In the Default value field, enter the value of the discriminator column corresponding to this sub-entity. For a sub-entity handling the salaried employees enter “S” as shown below.
- Click Ok, and repeat steps 4-8 for Commissioned employee entity.
- For each sub-entity, you can add the business logic you want.
- For common business logic, you can add it in the main entity object.