Here are a few guidelines for modeling and designing fact tables.

Fact Table Effective Practices

• The table naming convention should identify it as a fact table. For example:
  • Suffix Pattern:
    • <<TableName>>_Fact
    • <<TableName>>_F
  • Prefix Pattern:
    • FACT_<TableName>>
    • F_<TableName>>
  • Must contain a temporal dimension surrogate key (e.g. date dimension)
  • Measures should be nullable – this has an impact on aggregate functions (SUM, COUNT, MIN, MAX, and AVG, etc.)
  • Dimension Surrogate keys (srky) should have a foreign key (FK) constraint
  • Do not place the dimension processing in the fact jobs

Related References

• Data Modeling – What is Data Modeling?
• Where do data models fit in the Software Development Life Cycle (SDLC) Process?
• What is a Common Data Model (CDM)?
• Data Modeling – The Value of Data Indicator Flags
• Data Modeling – What is Data Modeling?
• Data Modeling – Dimension Table Effective Practices
• SQL Server – Basic SQL Server Object Naming Convention Guidance
• What are the Factor Affecting the Selection of Data Warehouse Naming Convention?
• Database Table Field Ordering Effective Practices