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
- Suffix Pattern:
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
Pingback: Database – What is a Composite Primary Key? | Scientia Compendium
Pingback: Database – What is a Primary Key? | Scientia Compendium