DataModelling Question about Contract and Orders in same Table

[Nocto]

Hey Guys,

first of all my My Goal is to develop a Snowflake scheme with a Big Fact Table. 

right now im working on a new Datamodell for the Waste Industry which uses Microsoft NAV and i have Problem. To simplifiy:

first Problem: Contract and Orders are found in the same table and the fields are all the same. To link both of them I thought that I extract the orders out of the Table so that i get 2 Tables: one for Contract, one for Orders. But then How do I generate a big Fact table when Orders and Contracts needs a connection?

Second Problem: on the Contract Levels are things happening that doesnt necessary have somethin to do with the Orders, example:

We have Customer A which made a contract with us that says that every three Months we retrieve their Waste Container and replace that with an empty Waste Container. This happens on the Contract Level and is priced of course.

Now Customer A has additionaly a specific Order where he is building a new facility and wants that we on date 5th march 2021 removed all detritus from the site. This happens on the Order Level.

So what would be the best Way to handle this connection? I read something about hierarchy, but I dont know if this is helpful. Any other Tip is much appreciated 🙂

regards

[marcus_sommer · Accepted solution]

IMO there is no need to split this table. That a fact-tables contained different level of data and/or granularities isn't per se a problem else often this is intentionally created in this way. If you miss some information between both layers you may create one or several mapping-tables from this table in beforehand to fill them within the main-load of this table.

- Marcus