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.
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.