I'm in the process of building a new data model and looking for some best practices. The problem I am having is with the Customer Orders (outgoing) and Purchase Orders (incoming) tables. Both contain very similar data such as Item ID, Quantity, and various dates (Order Entry Date, Requested Ship Date,...) I am already using the Canonical Date method to resolve the multiple dates to a single calendar. But it would seem that I would always get a circular reference due to the Item ID existing in both tables. Is it common to concatenate purchase orders with customer orders? Will I still need 2 master calendars? Open to suggestions
Scott, as you know , the same amount of information can be modeled in several ways. What's more , it's easier or harder with each of these models, you can achieve interesting analysis system . And that's where I think is the key factor to decide which of the possible models would be most appropriate in a given case . Of course, the best will be the model that will most effectively meet the expectations of the analysis. Therefore, without knowledge of the required KPI 's and work with the application scenarios, it is difficult to identify the best model . Of course, most random will be a star or snowflake, usually with one fact table . But for example, in your case , if the most important and most intensively analyzed would prove the relationship between order entering and exiting , perhaps it would be worthwhile , however, to keep them in separate tables or join . If , however, more interesting than the interrelatedness of contracts is generally their balance , then it is better to concatenate ... I hope that this thought will be helpful 🙂 regards Darek
