How to connect two fact tables to one dimensional table

Question

I have two fact tables (HistoricTable, ForecastTable). Both tables use a composite key that combines the productID and WeekID together. The ForecastTable has future weekID's & historic has previous ones. I want both of these ProductID's to reference one Dimensional table called ProductTable. How do I connect them?

My assumption would be to create an additional table that queries HistoricTable & ForecastTable via UNION join and have that connected to the ProductTable. Is this logic correct?

Table Image Layout

Answer 1

I want both of these ProductID's to reference one Dimensional table called ProductTable.

You can establish one foreign key between historic and product and another foreign key between forecast and product.

For example:

create table product (
  id int primary key not null,
  name varchar(10)
);

create table historic (
  product_id int not null references product (id),
  week_id int not null,
  units_sold int,
  primary key (product_id, week_id)
);

create table forecast (
  product_id int not null references product (id),
  week_id int not null,
  forecast_units int,
  primary key (product_id, week_id)
);