Struggling with Database Model Best Practice
Hello, My name is Peter and I am an accountant by trade but am keen on helping our organization transition to data-driven decisions. I am trying to set up a database for historical and forecasted oil and gas pricing. I want to try to use best practices as much as I can however I am having troubles identifying what that would be in this case. My issue All the records in dimension tables are used between the 2 fact tables (historic and pricing), however there are instances where 1 or more records in a dimension table will not appear in one of the fact tables. For example, only 7 of the 8 benchmark prices are in the forecasted table, only 2 of the 4 engineers are tracked in the historic fact table, etc. I am new to database modeling so forgive me if I am misunderstanding but I think a model with these characteristics would not be best practice because I am going to have records in my dimension tables that are not used in the fact tables. Question Does anyone have any recommendations or guidance for how to best structure this model (should I have a separate model for forecasted and historic pricing?) Ultimately I would like to be able to put this model into something like PowerBI and have the my dimension tables as filters that propagate to the other filters (i.e. if user selects Currency = USD, then only Any help/guidance to point me in the right direction would be much appreciated, thank you! Current Model Overview (no relationship to fact tables yet) More insights into the model (if useful) There are 2 types of oil and gas prices Oil prices Gas Prices Each price has multiple benchmarks (WTI, WCS, Brent, etc.) There are 4 different third party Engineers that provide these benchmark prices (1, 2, 3, 4) All 4 Engineers provider forecasted pricing but only on 7 of the 8 benchmarks Only Engineer 2 and 3 provide Historical Pricing Engineer 2 provides pricing on 7 of 8 benchmarks Engineer 3 provides prices on 8 of the 8 benchmarks Benchmark prices can be reported in CAD or USD For Forecasted Pricing, each Engineer reports the same 4 benchmarks in CAD and 3 in USD) For Historical pricing Engineer 2 supplies pricing for all their benchmarks in CAD while Engineer 3 provides pricing for 2 of their benchmarks in USD Ultimately the relationship structure looks like this HISTORICAL PRICING Price Type: Oil, NGL, Condensate Benchmark 1 Engineer 2 CAD Engineer 3 CAD Benchmark 2 Engineer 2 CAD Engineer 3 USD Benchmark 3 Engineer 2 CAD Engineer 3 CAD Benchmark 4 Engineer 2 CAD Engineer 3 CAD Benchmark 8 Engineer 2 CAD Engineer 3 USD Price Type: Gas Benchmark 5 Engineer 2 CAD Engineer 3 CAD Benchmark 6 Engineer 2 CAD Engineer 3 USD Benchmark 7 Engineer 2 CAD Engineer 3 CAD FORECASTED PRICING Price Type: Oil, NGL, Condensate Benchmark 1 Engineer 1-4: CAD Benchmark 2 Engineer 1-4: USD Benchmark 3 Engineer 1-4: CAD Benchmark 4 Engineer 1-4: CAD Price Type: Gas Benchmark 5 Engineer 1-4: CAD Benchmark 6 Engineer 1-4: USD Benchmark 7 Engineer 1-4: CAD
