How to use discriminator columns

Environment (JDeveloper, 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:

  1. Create an entity object based on EMPLOYEES table in hr schema.
  2. 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.
  3. Check the discriminator check box as shown below, then click OK.

    Discriminator Attribute

  4. In your Application Navigator area, right click on Employees entity, and select New Extended Object from the menu as shown below.

    Create sub-entity

  5. 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.
  6. 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.

    Override EmpType attribute

  7. 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.
  8. 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.

    Add the value of the discriminator column

  9. Click Ok, and repeat steps 4-8 for Commissioned employee entity.
  10. For each sub-entity, you can add the business logic you want.
  11. For common business logic, you can add it in the main entity object.


Filed under ADF

2 responses to “How to use discriminator columns

  1. wonderful
    رائع رائع رائع
    ما شاء الله عليك

  2. Bhaskar Kolloju

    Good one and explained in a simple way.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s