How to handle NULL Dates in an "Accumulating fact table"

Question

One type of fact table in Dimensional modeling is Accumulating Snapshot fact Table. if you think you need to review the meaning and definition of this type , you can take a look at this article : https://www.holistics.io/blog/the-three-types-of-fact-tables/.

Imagine we have an accumulating fact table which has three important date column in it

'Order_Datekey , Manufacturing_Datekey , Ship_Datekey'

When the table is being inserted for the first time ,there is no data for columns Manufacturing_Datekey and Ship_Datekey'. We only know the date in which the product has been ordered (Order_date). What I need to know is that how we can handle null values for Date columns ? The important point I read in Kimbal book is that null should be avoided in a foreign key column.

'Referential
integrity is violated if you put a null in a fact table column declared as a foreign key
to a dimension table'

On the other hand we don't have any value for these two date columns at first. What do you suggest?

Thanks in advance

Answer 1

You say "null should be avoided in a foreign key column," but are these dates really FKs?

Most likely, these dates are just attributes of an order, and if the dates are unknown (and the reason they are unknown may be "because it hasn't happened yet"), then NULL is the best way to represent those attributes.

If the Manufacturing Date or Shipping Date is your Foreign Key (or part of your Foreign Key) to other tables, then it would be an indication that you might want to rethink your table structure.