Master-Detail-Select with DB-Adapter where Foreign Keys different from Primary Keys

Imagine that you want to use the Oracle Database Adapter of the Oracle SOA Suite in a BPMN 2.0 (Oracle BPM Suite) or BPEL (Oracle SOA Suite) scenario to retrieve master-detail data. The adapter normally expects the foreign key in the detail table to equal the technical primary key of the master table. This post provides a workaround if you have a business foreign key for the master-detail relationship which differs from the technical primary key of the database master table.

The example is based on the HR schema. The default master-detail in HR schema is build out of REGIONS  (master, primary key REGIONS_ID) and COUNTRIES  (detail, with the primary key COUNTRIES_ID). Both are associated by the foreign key REGION_ID  in the COUNTRIES  table which is indeed the primary key of the REGIONS  table.

The configuration of the database adapter is straight forward according to the documentation. The important step in the Adapter Configuration Wizard is the definition of the relationship.

Now imagine that not the primary key of the master table is used to associate the detail. Instead, the master has an additional unique identifier defined. To underline this, the example below even defines this identifier to be a String value (VARCHAR2 as database data type) instead of the usually used NUMBER database type.

First of all, I have to admit that this is a poor database model design. But let’s face it: Especially in historical grown structures, those things can happen and redesigning the database model is often out of discussion.

The problem is now that the database adapter is not supporting such a master-detail relation. Even if you can select any field as foreign key of the detail, you cannot change the primary key of the master.

So what options do we have to overcome this limitation?

1. Redesign of the database model to make the unique business identifier of the REGIONS  to become the primary key of the REGIONS3  table. First of all, VARCHAR2-typed columns are no usual primary keys of database tables. But let’s assume the unique business identifier is a numeric value as well. Even the (as described above), redesigning the database model is often out of question.
2. Write your own SQL statement. It’s the most accurate way. But the disadvantage is that you are leaving the “zero-code” approach. Regarding maintenance and reuse, it’s better to stick to “drag & drop and wizard-click-through-configuration”.
3. Write your own JCA database adapter. Regarding the power of the Oracle Database Adapter and reusability, this is obviously the worst choice. But for the sake of completeness, let’s name it anyway.
4. Use database synonyms. That’s actually the workaround I would suggest. I admit that it is a little bit “dirty” but it has the least impact to the “zero-code” approach.

The relationship configuration in the database adapter wizard is based upon the definition of primary keys. If you are working with database tables, the wizard retrieves the technical primary keys of the database tables to be the primary keys for any relationship definition. But if you are using synonyms, you are free to define your primary keys on your own. You can now select your unique business identifier REGION_NAME  instead of the technical table id REGION_ID .

Consequently, you are able to define your master-detail relation based on this primary key.

Once configured the master-detail relationship according to your needs, you can now proceed to finalize your database adapter configuration to retrieve the master-detail selected by your unique business identifier.

Reminder: Make sure your synonyms are defined as direct representation of the according database tables. You will not be able to perform all CRUD operations if your synonym associates database views. A well-known best practice to keep track of your different types of synonyms is to add prefixes: “V_” for synonyms representing database views, “T_” for synonyms representing database tables (as used in the above example) and so on.