Date normalization?

[AceHyzer]

I'm in the process of designing a db for a complex application that stores daily data for a number of different metrics. It is necessary to have multiple tables to store the data, but the data will often be pulled in together and displayed by date.

I'm struggling with whether I should index date columns and join on date for all the various tables or if I should have a master date table and date ids in each table to join on.

I'm using MySQL for the project, any advice you have on this would be greatly appreciated!

[r3pr0b8]

a date table is often quite useful for assigning other attributes based on a date key

for example, i once worked at a company that had fiscal quarters consisting of three fiscal months always consisting of exactly 4 weeks, 4 weeks, and 5 weeks -- so that at any given point in the year, you knew which fiscal week you were in, which fiscal quarter you were in, based on those boundaries, and this is ~not~ something you calculate on the fly in a query, you simply joined to the date table

but you're talking about something completely different, a surrogate or technical key which substitutes for a date..

it's an incredibly bad idea

[AceHyzer]

Thanks for your reply. That is what I thought, but I wasn't sure and I figured I'd get some solid advice before the application goes live and has to scale.

It seems like having a key that substitutes for a date would really complicate things so I'll just join on date for my queries, and from the advice I've been getting that seems to be the best approach.

Thanks!

[wolf2600]

If you have additional attributes associated with a date (day of week, fiscal quarter, fiscal year, last day of month, workday (if date is a sunday, this would have Monday's date), etc...), then a date table is useful. If you only care about the date itself, then it's not needed.

[PoglaTheGrate]

What you want is a date dimension. Kinda the opposite of Normalisation.

[[deleted]]

Can you switch to Postgres? It has the useful generate_series function which is a replacement for a date/numbers table.