The Oracle Billing and Revenue Management (BRM) database structure can be confusing at first glance. Rather than following a typical relational design, BRM was designed to support an object-oriented approach for extensibility.
This post describes the logical relationships that exist between tables so you can get started.
First, a table is either a Parent table or a Child table. The Parent tables are usually one word table names with “_t” on the end. For example:
- Account_t
- Bill_t
- Item_t
- Event_t
- Service_t
- Profile_t
- Payinfo_t
The Child tables’ names always start with their Parent table’s name. For example:
- Account_nameinfo_t (child of account_t)
- Account_products_t (child of account_t)
- Event_billing_products_t (child of event_t)
- Payinfo_cc_t (child of payinfo_t)
Parent tables are logically joined to their Children tables by the poid_id0 (please note that’s a zero at the end of the poid_id string) column in the parent and the obj_id0 column in the Child table. Here’s a sample select statement:
Parent_table.poid_id0 = child_table.obj_id0
For example, account_t.poid_id0 = account_nameinfo_t.obj_id0
Some Child tables will join back to other Parent tables. For example account_products_t joins to the service_t table by account_products_t.obj_id0 = service_t.account_obj_id0 and account_products_t.service_obj_id0 = service_t.poid_id0. These are bi-directionally joined.
===================================================
That above paragraph addresses parent/child table relationships in BRM. But how do parent tables relate to other parent tables?
Parent tables are joined together by their poid_id0 columns.
Not all parent tables join to each other but when they do it will always be parent_table.poid_id0 = joined_parent_table.
For example, account_t and service_t are joined by account_t.poid_id0 = service_t.account_obj_id0
It is always easy to see the relationship in the tables by looking at the columns. Poid_id0, obj_id0, and anything that ends in obj_id0 (like account_obj_id0, service_obj_id0, etc) are the joined columns.
