Menu
I am working on my first dimensional modeling assignment for a Data Warehouse project using Kimball's approach. As I prepare my model and think about physical objects, I wonder what is the recommended naming scheme for database objects. We're going to use Oracle, and we don't really have any standards at present. Any help would be appreciated.
682
What Is a Naming Convention? In simple terms, a naming convention refers to a framework used for naming your files in a specific way. This should be descriptive and consistent throughout the organization. It is always best to use a naming convention to describe the contents of the files.
Fact tablesForeign key names should start with FK_ and the name should reference either the table it links to or the primary key column in that table. If a fact table has more than one primary key, use numbers in the PK_ prefixes.
You can take some ideas from the Oracle BI Applications Data Model. Log in to your Oracle support account and look for this document: Oracle Business Analytics Warehouse Data Model Reference Version 7.9.6.3 (Doc ID 1325948.1)
These are some of the naming conventions included:
PREFIX
W_ = Warehouse
SUFFIX
_A = Aggregate
_D = Dimension
_DH = Dimension Hierarchy
_DHS = Staging for Dimension Hierarchy
_DS = Staging for Dimension
_F = Fact
_FS = Staging for Fact
_H = Helper
_MD = Mini Dimension
_TMP = Pre-staging temporary table
For example: Sales fact table would be W_Sales_F
This document from northwestern university has useful tips for naming columns, such as using prime, qualifier and class words (e.g. STUDENT_FIRST_NAME)
The kimball group's design tip #71 contains general guidelines for naming conventions
For example, a sales analyst would be interested in Sales numbers, but it turns out that this Sales number is really Sales_Commissionable_Amount, which is different from Sales_Gross_Amount and Sales_Net_Amount.
75
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
