skip to main content
About the Driver : Progress DataDirect for JDBC for Oracle Service Cloud : Mapping Objects to Tables : Primary Objects, Sub-objects, and Lists of Sub-objects
  

Try DataDirect Drivers Now
Primary Objects, Sub-objects, and Lists of Sub-objects
To map the Oracle Service Cloud object data model to a relational data model, the driver decomposes primary objects into parent-child tables. The fields of a primary object and the fields of its associated sub-objects are exposed as columns in a parent table, while lists of sub-objects and their fields are exposed as a child table.
For example, an Oracle Service Cloud data store has a primary object called EMPLOYEE which takes the following form:
EMPLOYEE (ID, NAME, EMAIL, PHONES, HIREDATE)
The driver decomposes this primary object into two separate but related tables. Where the NAME sub-object contains the fields FIRST and LAST, the driver exposes corresponding columns in an EMPLOYEE parent table using the <objectname>_<fieldname> pattern:
EMPLOYEE (ID, NAME_FIRST, NAME_LAST, EMAIL, HIREDATE, PRIMARY KEY (ID))
In turn, where PHONES is a list of sub-objects with NUMBER and TYPE fields, the driver exposes a distinct yet related child table:
EMPLOYEE_PHONE (EMPLOYEE_ID, NUMBER, TYPE, FOREIGN KEY (EMPLOYEE_ID)
REFERENCES EMPLOYEE(ID) ON DELETE CASCADE)
As the example shows, the name of the child table is concatenation of the parent table name and the name of the list of sub-objects. The example further shows that the driver maintains the relationship between parent and child tables by exposing the parent table's primary key as a foreign key in the child table. The name of this referential column is a concatenation of the parent table's name and the name of the parent table's primary key column joined by an underscore (_) separator.