Associating date fields from 2 fact tables

[sifatnabil]

Hi, on fact table 1 I made the the dates like this:

Fact1:

CONVERT(VARCHAR(10),DealTime,3) as DATESTAMP,

datename(Month,DealTime) as MONTH,

datename(Year,DealTime) as YEAR,

datepart(week,DealTime) as WEEKNUMBER,

datepart(day,DealTime) as DAY

Fact2:

... as DATESTAMP,

... as MONTH,

... as YEAR,

... WEEKNUMBER,

... DAY

This creates an unsightly model:

The model works though but Is this efficient? Is link table the solution?

[tresesco · Accepted solution]

Link table could be an option. But, probably this suggested canonical approach could be a better choice in general.