I have a need to store products along with their prices and quantities (and many other things) and track the history.
Now, given both price and quantity are stored as integers, it is my understanding these are fact measures. So we might have a table like:
f_products:
id
sku
price
quantity
As the price varies over time we can insert a new fact, and I can introduce effective dates to the table to allow for querying the price or quantity at a given time.
But with this design, it means if the quantity changes, I must insert another row, repeating the unchanged price value again. And while this isn't too bad for 2 columns, I have in fact 10.
So, my design is:
f_products:
id
sku
price:
id
product_id
price
start_date
end_date
another "dimension":
id
product_id
value
start_date
end_date
and so on
Now the "fact" no longer needs to change when the price changes, and I can query for historical values across these tables.
So my question is, this doesn't seem to fit the standard fact / dimension designs, so I wonder how more experienced modellers would approach these requirements.