6

If I have a table like this:

my_table
=======================
id    date    some_data

Does it make any sense to pull the date out to it's own table? The date field would use the date data type. So it would now look like this:

my_table
==========================
id    date_id    some_data

dates
==========
id    date

According to MySQL, date data type = 3 bytes. The date_id would need to be a max of MEDIUMINT (3 bytes) in order to get the same result. A normal INT or BIGINT exceeds the storage requirements of a DATE type.

A downside to a separate table is that the queries would be more complicated because I am joining another table.

Are there any other ramifications that need to be taken into account as to whether or not to split the date field into it's own table? My gut reaction is that it's not worth doing unless you are dealing with a small set of dates (small enough to use TINYINT or SMALLINT).

Improve this question
2
  • What are you trying to achieve with this? Save space? Is the table expected to have millions of rows?
    – Ezequiel Tolnay
    Commented Apr 5, 2016 at 5:06
  • 1
    A separate date table is a feature of star schema data warehouse designs. In that setting, however, they tend to have additional columns like DayOfWeek, Month, Quarter, FinancialYear and such. These help produce aggregates at the cost of more storage. In an OLTP setting I have never seen this done.
    – Michael Green
    Commented Apr 5, 2016 at 10:54

2 Answers 2

Reset to default
8

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.

Improve this answer
1
  • 1
    Agreed, the only reason I see to do this if you somehow were sharing this fact with many source ids, just going one step too far in "normalizing".
    – ConstantineK
    Commented Apr 4, 2016 at 23:06
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.