In our business, we have Orders that are made up of OrderItems. Over time, these Orders can change status (e.g. Received, Challenged, Planned, Completed). During this process, the OrderItems for an Order can be changed, added, or removed.
For example, when an Order is finally fulfilled, the OrderItems might actually be for different Items and different prices to the OrderItems on the original Order.
I'm wondering how to model this. Any suggestions welcome!
Best Answer
I should maybe create a list only with OrderNumber and Status column. Then I would create a mechanism that will delete all changed non-completed orders and load them again and another mechanism which will load newly completed Orders. I think the specific structure really depends on the structure of you Dw (how often do you perfrom loading, how many layer your solution has etc.)