What would you call a fact table with effective from date and to date?

Question

A slightly silly question in some ways as this is just about terminology.

If I have a fact table that records facts that change overtime with respect to the dimensions the fact belongs to, what would such a fact table be called? In fact is there a particular name?

For example the fact table might be storing an instance of a ticket in a help desk system and have a Status dimension. The ticket would change status over the course of it's lifecycle from Open to Resolved. I need to be able to store a fact record for each state change for historical reporting.

Answer 1

If you have a limited number of status changes (< 6), you could put all of the dates in one row of the fact table. It's ok to denormalize a warehouse fact table.

In an operational data base, I'd call this type of domain table a date range table.

This book might be helpful: Temporal Data & the Relational Model.

You can also Google "temporal database" and see if anything turns up helpful to you.