Environment (JDeveloper 220.127.116.11.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:
- A student can take many different courses, and each course can be taken from many different students.
- 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:
- from the right click menu of your entities package node, select New Association.
- for the association name, enter EmployeePastJobsAssoc and click next.
- 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.
- 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.
- click Next for other windows, you can accept the default configuration. Then click Finish button.
- 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.
- right click on your view package node and select New View Link from the menu. you must see the Create View Link window.
- on the name window enter EmployeePastJobsLink in the name field and click next.
- 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.
- in View Link Property window accept the default and click the Finish button.
- 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.
- 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.
- select Run from the right click menu on your Application Module.
- double click on EmployeePastJobsLink1 to see the employee and his/her past jobs as shown below.
- double click on JobHoldersLink1 to see the Job and its employees’ holders as shown below.