how to update a fact table?

Question

I created a fact table that has a date column. I am trying to create a dimension date table that contains the month, year and quarter of this date column. I did the following:

    create table if not exists date_dimension(
    date_id int auto_increment primary key,
    year smallint not null,
    quarter smallint not null,
    month smallint not null);

Then i updated this table from the fact table as such:

    insert into date_dimension(year, quarter, month)
    select year(orderDate) as year, quarter(orderDate) as quarter, month(orderDate) 
    as month from facts as f
    group by year(orderDate), quarter(orderDate), month(orderDate)

How do i replace the date column with the primary key i created? i.e make it the foreign key from the date id column? the date column is of form:

2001-01-06

for example

Answer 1

Standard practice is to populate a date dimension, when you create it, with all possible dates any of your fact tables could possibly use over the lifetime of your application.

The PK for the date dimension table should be the date - this is the one case when you don’t use a surrogate key