Oracle – Meaning for different table names ending with (_B,_TL,_VL,_F)


Note that there are quite a few exceptions to the rules. It’s just a convention:

_B – base table. It holds the main details of the object in question and it implies that there is also a _TL table for it. You’d never find a _B table without a _TL table. the primary key is the id column.

_TL – translatable table. Holds the translated names/descriptions for base objects. will have one row per installed language. linked to the base table by the _id column. Doesn’t always imply that there is a _B as its base data could also be in a _F table or a table without postfix. The primary key is the id column plus the language column.

_F – date effective table. Tends to exist only in HR and it holds objects that are date effective. It means the primary key is the combination of the ID column, effective start date and effective end date. there is only 1 row per ID for any date, but you can have multiple rows for the same id over time. Some _F columns come with a matching _TL column, too.

_VL – translatable view. It’s not a table, but the view that represents the join of the base table and the TL table using the user’s session language. You can assume that almost all _TL tables come paired with a _VL view.

_M – multiple changes per date table. Like a _F table, but it allows rows to start and end on the same day. There is only one such table per_all_assignments_m, and it has additionally the effective_sequence in the primary key.

_DN – denormalised table: usually just a simplification of another table that holds hierarchies or trees of some kind. The denormalised tables are populated by ESS processes and hold the flattened hierarchy that has direct links between all nodes and their distance. Those are used e.g. for security profiles based on the manager hierarchy.

Advertisements