Is it worth making a separate DATE table or not?
Seems to me that is a classic example of over-thinking your design.
Keep the dates in the same table so they can be easily indexed with other columns if necessary, and not take up 3 times as many bytes, and not make life a living hell for future developers.
Normalization is often a good idea, either for flexibility or space. But Normalizing "continuous" values, such as DATE
, DATETIME
, FLOAT
, etc, is generally a mistake. Don't do it.
Perhaps the biggest problem occurs when you decide to filter on a date range, and you find the JOIN
is killing performance.
Even if you had fewer than 255 dates (I'm thinking of TINYINT UNSIGNED
), DATE
is not worth turning into an id.
While we are talking about ids, generally they should be UNSIGNED
and NOT NULL
. And almost never is BIGINT
warranted.