This goes back to the role playing that I mentioned.

Dimensions can play different roles in a fact table depending on the context. For example, the Date dimension can be used for the ordered date, scheduled shipping date, shipment date, and invoice date in an order line fact.

In the data warehouse, you will have a single dimension table for the dates. You will have multiple warehouse foreign key from the fact table to teh same dimension.

The following code demonstrates this from the relational perspective:

CREATE TABLE DimCountry (

Country_Code INT NOT NULL

,Country_Name Varchar(200) NOT NULL

,CONSTRAINT PK_DimCountry PRIMARY KEY CLUSTERED (Country_Code)

)

CREATE TABLE Fact_Table (

Fact_ID INT IDENTITY(1,1) NOT NULL

,Important_Fact INT NULL

,Origin_Country INT NOT NULL

,Destination_Country INT NOT NULL

,CONSTRAINT PK_Fact_Table PRIMARY KEY CLUSTERED (FACT_ID)

,CONSTRAINT FK_FT_Origin FOREIGN KEY (Origin_Country)

REFERENCES DimCountry (Country_Code)

,CONSTRAINT FK_FT_Destination FOREIGN KEY (Destination_Country)

REFERENCES DimCountry (Country_Code)

)