Skip to main content
added 11 characters in body
Source Link

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 withinstead, 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?

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 with 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?

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?

Source Link

Organize repeating timestamped values in schema

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 with 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?