5

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).

  • What are you trying to achieve with this? Save space? Is the table expected to have millions of rows? – Ziggy Crueltyfree Zeitgeister Apr 5 '16 at 5:06
  • 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 Apr 5 '16 at 10:54
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.

  • 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 Apr 4 '16 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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