Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Naming standards for dimensional modeling

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.

like image 682
vikc Avatar asked Feb 19 '14 15:02

vikc


People also ask

What are naming conventions?

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.

How do you name a fact table?

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.


1 Answers

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.

like image 75
Victor HDC Avatar answered Oct 27 '22 19:10

Victor HDC