How to Implement many to many association

Environment (JDeveloper 11.1.1.5.0, ADF BC, hr schema)

Many to many relationship can’t be represented in the physical database (i.e. in the actual database table) because you can’t just associate one key from one table with another key from the other table.  However, many to many relationship can be broken down into a series of two or more of one to many relationships through a table called intersection table. Intersection table is simply a table that contains the primary key of each of the table being connected. Consider the followoing cases as examples about many to many relationships:

  1. A student can take many different courses, and each course can be taken from many different students.
  2. An employee can change his job several times during his career life, and each job can be held by several employees.

In this example I will explain how to implement a many to many association based on hr schema. As mentioned above, each employee can have different jobs at different time interval and each job can be held by several employees . To implement this, I will assume that you have already built your BC, we need (Entity objects, View objects) for these database tables:EMPLOYEES, JOBS, JOBS_HISTORY. The steps to implement many to many association are:

  1. from the right click menu of your entities package node, select New Association.
  2. for the association name, enter EmployeePastJobsAssoc and click next.
  3. in the Entity object window, select * to * as the cardinality of the association. And from the Select Source Attribute area, expand the Employees entity and select EmployeeId attribute. From the Select Intersection Attribute area, expand the JobHistory entity and select the EmployeeId attribute then press the Add button as shown below.

    create EmployeePastJobsAssoc association

  4. from the Select Destination Attribute area, expand the Jobs entity object, then select the JobId attribute. From the Select Intersection Attribute, expand JobHistory entity and select the JobId attribute then press the add button as shown below, then click the Next button.create EmployeePastJobsAssoc association
  5. click Next for other windows, you can accept the default configuration. Then click Finish button.
  6. repeat the steps from 1-5 to create JobHoldersAssoc, but make sure to select the JobId from the Jobs entity from the Select Source Attribute area with the JobId from the JobHistory entity in the Select Intersection Attribute area. And select the EmployeeId from the Employees entity in the Select Destination Attribute with the EmployeeId from JobHistory entity in the Select Intersection Attribute area as shown below.

    create JobHoldersAssoc association

  7. right click on your view package node and select New View Link from the menu. you must see the Create View Link window.
  8. on the name window enter EmployeePastJobsLink in the name field and click next.
  9. in the View Object window, select * to * as the cardinality of the view link. In Select Source Attribute area, expand the EmployeesView node and select the EmployeePastJobsAssoc node. In Select destination Attribute area, expand the JobsView node and select EmployeePastJobsAssoc node then press the Add button as shown below. Click the Next button.

    create EmployeePastJobsLink view link

  10. in View Link Property window accept the default and click the Finish button.
  11. repeat the steps 7-10 to create JobHoldersLink association. Make sure to select the JobHoldersAssoc  from JobsView in the Select Source Attribute area. And select JobHoldersAssoc  from EmployeesView in the Select Destination Attribute area as shown below.

    create JobHoldersLink view link

  12. now open your Application Module, then open the Data Model section. shuttle EmployeesView and JobsView via EmployeePastJobLink to the Data Model area. And shuttle the JobsView and EmployeesView via JobHoldersLink to the data Model area as shown below.

    Add the views to Data Model

  13. select Run from the right click menu on your Application Module.
  14. double click on EmployeePastJobsLink1 to see the employee and his/her past jobs as shown below.

    Employees and his/her past jobs

  15. double click on JobHoldersLink1 to see the Job and its employees’ holders as shown below.

    Jobs and holders


Advertisements

Leave a comment

Filed under ADF

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s