The sections below describe our naming standards for Star Schema layers in the Optimal Data Engine. Whatever is on this web page is our current version.

 

Star Schema Design Principles

  1. If the data is not being manipulated then the column name should not change.
    (This principle will occasionally need to be broken for clarity)
  2. Always use full descriptive names ie. Brand Name. and don’t use abbreviations. Otherwise you may need to spend time training users.
  3. Don’t use Dim or Fact in your table names because you end up spending a lot of time deleting these from the reporting layer.
  4. Don’t use camel case or underscores, use spaces. Yeah I thought this was bizarre, but again they said you’ll spend a lot of time adding in space at the reporting layer and most databases now handle spaces in column names without a problem.
    (2-4 are taken from here)

Columns

Generally star schema tables have the following columns:

Name Datatype Nullable FK On Admin Tables Comment
<table_name>Key int N Y Surrogate Key
Identity column
Only on dimensions!
Business “key” ? N N May be composed of multiple columns
CreatedByKey int N Party N
CreatedDateKey int N DateDay N
CreatedTimeKey smallint N DateTime N
UpdatedByKey int Y Party N
UpdatedDateKey Int Y DateDay N
UpdatedTimeKey smallint Y DateTime N
RowIsCurrent char N Y Computed
RowStartDate datetime2 N Y
RowEndDate datetime2 N Y
RowChangeReason Varchar Y Y
InsertAuditKey bigint N Execution Log N
UpdateAuditKey bigint Y Execution Log N