beVault naming convention
When working with beVault, understanding the naming conventions for database tables and columns is essential for effective data management and query construction. beVault employs a consistent, structured approach to naming that follows specific patterns based on the type of data being stored and its relationship to other entities in the system.
These naming conventions serve several important purposes:
Consistency: By following standardized patterns, beVault ensures that all database objects maintain a uniform structure, making the schema more readable and predictable.
Self-documentation: The names themselves convey information about the purpose and relationships of the data they represent, reducing the need for separate documentation.
Query optimization: Consistent naming facilitates more efficient query construction and reduces the likelihood of errors when referencing database objects.
Maintenance: As the system evolves, consistent naming conventions make it easier to identify, modify, and extend database structures without disrupting existing functionality.
Currently, beVault uses hardcoded naming conventions throughout its database architecture. This document outlines these conventions to help you understand how tables and columns are named, the rationale behind these patterns, and how to interpret them when working with beVault's data structures.
The following sections will detail the specific naming patterns for different types of database objects, including primary tables, relationship tables, system tables, and the various column types that appear within them.
Categories of naming conventions & configuration
Schema
Constraint
Column
Table
Flags and configuration
Naming convention for schemas
Schemas | Description | Type of convention | beVault convention |
|---|---|---|---|
Meta | Schema where the metadata encoded in beVault will be deployed | Constant | meta |
Source | Schema used as a source system for the data quality results | Constant | src |
Staging | Schema where the staging tables are deployed | Constant | stg |
Raw Vault | Schema where the raw vault’s entities such as hubs and links will be deployed | Constant | dv |
Business Vault | Schema where business vault’s entities such as pit, bridges and effectivity satellite will be deployed | Constant | bv |
Information Mart | Default information mart schema. | Constant | im |
Naming convention for constraints
Constraints | Description | Type of convention | beVault convention |
|---|---|---|---|
Index | Naming convention for the indexes (if applicable) | Prefix | 'idx’ |
Foreign Key | Naming convention for the foreign keys (if applicable) | Prefix | ‘fk’ |
Primary key | Naming convention for the primary keys (if applicable) | Prefix | ‘pk’ |
Unique Index | Naming convention for the unique indexes (if applicable) | Prefix | ‘uidx’ |
Naming convention for columns
Column | Description | Type of convention | beVault convention |
|---|---|---|---|
Recording source | Name of the column containing the recording source | Constant | src |
Snapshot date | Name of the snapshot date column in pit and bridge tables. | Constant | snapshot_date |
sequence | Name of the sequence column in staging lvl2, pit and bridges | Constant | seq |
business key | Name of the business key column in the hub tables. | Constant | bk |
Reference business key | Naming convention used for the hub reference columns in pit and bridge tables. | Suffix | bk |
Hash key | Name of the hash key columns | Constant | hk |
Reference hash key | Naming convention used for the hub reference hash key columns in link, pit and bridge tables. | Suffix | hk |
Link hash key | Name of the hash key column in link tables | Constant | hk |
Reference link hash key | Naming convention used for the link reference hash key columns in pit and bridge tables. | Suffix | lhk |
Dependent child | Naming convention used for the dependent children of the links | Suffix | |
Hash diff | Naming convention used for the hash difference columns of satellites and pit tables | Suffix | hdiff |
Load date | Name of the load date columns | Constant | load_dts |
Load end date | Name of the computed load end date column in satellite views | Constant | load_end_dts |
Reference load date | Naming convention used for the load date reference columns in pit and bridge tables. | Suffix | load_dts |
Reference load end date | Naming convention used for the load end date reference columns in pit and bridge tables. | Suffix | load_end_dts |
Current flag | Name of the column identifying current records in effectivity satellites | Constant | is_current |
Start date | Name of the start date column in effectivity satellites | Constant | start_dts |
End date | Name of the end date column in effectivity satellites | Constant | end_dts |
Data grouping key | Name of the grouping key column in multi-active satellite tables.. | Constant | group_key |
Naming convention for tables
Table | Description | Type of convention | beVault convention |
|---|---|---|---|
Source | Prefix | src | |
Staging level 1 | Prefix | ||
Staging level 2 | Suffix | l2 | |
Reference table | Prefix | ||
Hub | Prefix | h | |
Link | Prefix | l | |
Non-historized link | Prefix | l | |
Same-as link | Prefix | l | |
Hierarchical Link | Prefix | l | |
Satellite | Prefix | s | |
Hub’s satellite | Prefix | sh | |
Link’s satellite | Prefix | sl | |
History | Naming convention used for the views created for each satellite to compute the load end date | Prefix | |
Effectivity Satellite | Prefix | effsat | |
Multi-active satellite | Prefix | mash | |
Pit | Prefix | pit | |
Control | Naming convention used for the control tables containing the list of snapshot dates | Prefix | ctrl |
Naming convention for system records
Record | Description | Type of convention | beVault convention |
|---|---|---|---|
Ghost Record source | Value of the recording source column for the ghost record | Constant | SYSTEM |
Null Business Key expected source | Value of the recording source column for the ghost record of expected business keys | Constant | SYSTEM |
Null Business Key optional source | Value of the recording source column for the ghost record of optional business keys | Constant | SYSTEM |
Flags and configuration
Record | Description | Type of convention | dFakto DV2.0 convention |
|---|---|---|---|
Hub Hash Key Format | Hashing algorithm used to compute the hash keys of the hubs | Constant | MD5 |
Link Hash Key Format | Hashing algorithm used to compute the hash keys of the links | Constant | MD5 |
Satellite Hash Diff Format | Hashing algorithm used to compute the hash diff of the satellites | Constant | MD5 |
Hash separator | Separator used between fields during the computation of the hash | Constant | ;; |
Dawn of time | Earliest date used for the load date of the ghost records | Constant (datetime) | 0001-01-01 00:00:00 |
End of time | Oldest date possible used for the get the current data with satellite historical views and in the effectivity satellites | Constant (datetime) | 9999-12-31 00:00:00 |
Store date as UTC | Constant (boolean) | true | |
Enforce integrity | Define if foreign keys should be created or not | Constant (boolean) | true |
Add unique index on hub business key | Creates a unique index on the hub's business key to ensure uniqueness | Constant (boolean) | true |
Hub Ghost record business key | Business key for the default ghost record | Constant | 0 |
Business Key expected ghost record | Business key for the ghost record when the reference is expected | Constant | -1 |
Business Key optional ghost record | Business key for the ghost record when the reference is optional | Constant | -2 |
Pit Source value | Value of the recording source column in pit tables | Constant | SYSTEM |