Data Model for Stock quantity

Question

I would like to ask you about how to design a data model for books stock quantities. I tried to figure it out, but I finished with cartesian product, and for 300 books and for few years the stock fact table was a really huge. What is important, this solution could provide me an option I can see the stock quantities to specific date or period.

I searched a while and I also tried a read The Data Model Resource Book, Vol. 1, but none of this help me too much.

But I think my solution is not right because libraries could have millions of books.

---

For start, there is the db<>fiddle example.

Purchases fact table: What book was purchase to library store

purchase_key	date_key	book_key	cost
1	20231101	1	900,0
2	20231201	2	750,0

Orders fact table: What book was sold to customer

order_key	date_key	book_key	price
1	20240101	1	1080,0
2	20240101	2	900,0

Books dimension: Exact book exemplars

book_key	book
1	A
2	B

Dates dimension

date_key	period_key
20231101	202311
20231201	202312
20240101	202401

With the created view with cartesian product and the last select below I can let the report user filter the whole model.

select
  t1.period_key,
  t1.book,
  max(t1.delta)
from (
select 
  s.date_key,
  d.period_key,
  b.book,
  s.buys,
  s.sells,
  s.cum_buys,
  s.cum_sells,
  s.delta
from stocks s
  left join dates d on (d.date_key=s.date_key)
  left join books b on (b.book_key=s.book_key)
) t1
group by
  t1.period_key,
  t1.book
;

What is important, the user knows, that for period=202312 there is still 1 quantity of A book on the stock. This book was bought 202311.

period_key	book	max(t1.delta)
202311	A	1
202311	B	0
202312	A	1
202312	B	1
202401	A	0
202401	B	0

period_key	book	max(t1.delta)
202312	A	1
202312	B	1

But without the cartesian product a with filter above, the user would not see the purchases before.

---

I would like to thank you all in advance for your constructive advices

Best regards Ondrej