I am creating models to hold some financial data. The information frequently is split into timestamped values, e.g:
Some value, now | as of Jan 1st | as of Jun 1st | as of Sep 1st
There are four timeframes per value; for each of them values will differ, but conceptually mean the same thing. I have around 30 of different 'timeframed' values to store. Timeframes in each case are usually the same, but technically can change. There is also some not timestamped stuff to store in addition to the above.
How to model this into db schema in order to keep it clean, elegant and efficient? Right now it's hard to anticipate the way this data will be used and what type of queries will be most frequent.
I'm considering flat schema to make querying as easy as possible (no joins), so the columns would be:
id | value_now | value_now_date | value_previous | ...
... but the model gets huge and I'm tempted to add separate table instead, having columns:
id | FK to main model | value_name | date_frame
Are there significant benefits of such approach? Or the previous one? Are there other options I should consider?