Using the DATE field as the primary key for measuring date using MySQL

Question

I want to process a date dimension in a MySQL data warehouse. (I'm new to the DW world)

I did some google searches and saw many table structures (most) when the primary key is a simple UNSIGNED INTEGER .

Why not use the DATE field as the primary key, since with MySQL it is 3 bytes VS 4 bytes for INTEGER ?

Example:

 CREATE TABLE dimDate id INTEGER UNSIGNED NOT NULL PRIMARY AUTOI_NCREMENT, date DATE NOT NULL, dayOfWeek ... 

VS

 CREATE TABLE dimDate date DATE NOT NULL PRIMARY, dayOfWeek ... 

Answer 1

If you have a table with a column of type date and where two rows will never have the same date, you can use this column as a PRIMARY KEY .

You see many examples where the primary key is a simple UNSIGNED INTEGER , because there are many cases where there is no ideal candidate for the Primary key. AUTO_INCREMENT allows this column to be automatically populated by the database (and be unique) during insertions.

Answer 2

Date size is a special option - having a date (2011-12-07) or an integer associated with a date (20111207) for the primary key is actually preferable. This allows a good separation (by date) of the fact table.

For other types of sizes it is recommended to use surrogate (whole) keys.

As a template, each size usually has entries for unknown, not entered, error, ... , which are often mapped to keys 0, -1, -2, ...

In this regard, most often finding a date with an integer (20111207) as the primary key instead of the date is a bit random to represent unknown, not entered, error, ... with a date type key.