Now I am working on my project I need to create a data warehouse, we have an application where a client can place an order, One order can be sent in multiple deliveries(even for the same product for example if he asked for 5 when can send the first time only 3 and the second time we will send the rest ).
Now I am thinking of creating two fact tables: Order (client,order_id, product, quantity,dateOrder)and should I add a new column to design if the order is canceled or treated, or being treated(if we send part of the order) so this column should be changed we can add a new column date change. Delivery (client,orderid,ShippingDateKey ,OrderDateKey,ProductKey,QuantityShipped, quantity NeedTobe shipped,changeDate)
OR I should create only one fact table that has all the information(orderdate,customer ,product ,shipdate'but first it is Null!!!' and it will be changed each time we deliver a part of the order of this product,shiped quantity,orderquantity).
PS OrderKey and OrderLineNumber should it be in a dimension or we can add it to our fact table as Primary key?
Second thing wether i am going to use first or second method we gonna update information of our DW but we know that we don't have the right to delete or change anything from DW!!
Thank you