1

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?

Improve this question
7
  • The table looks good from the point of complexity, but it depends how the data will be used (selected, updated, etc.). I think in some cases flat will be much quicker. From my point of view second chose is better, but once again it depends.
    – Bogdan Bogdanov
    Commented Apr 29, 2015 at 6:26
  • Then is there difference in which choice would provide easier migrations to/from? Or again, it's entirely possible I miss some other option that would make more sense.
    – zencodism
    Commented Apr 29, 2015 at 18:55
  • What do you mean by 'creating models'? Do you have to maintain a rolling window? Once a record has been inserted will it change? Will you be adding more data to this model? What tools/programs will be using it? Reports? Apps? Excel pivot tables?
    – Sir Swears-a-lot
    Commented Apr 30, 2015 at 10:21
  • "Now" bothers me. Todays "now" will be yesterday tomorrow.
    – Sir Swears-a-lot
    Commented Apr 30, 2015 at 10:24
  • What database platform are you intending to use?
    – Sir Swears-a-lot
    Commented Apr 30, 2015 at 10:33

2 Answers 2

Reset to default
1

I'd go with the 2nd approach. Don't be afraid of Join(t)s. Here are some reasons

  • Fast enough. Chances are extremly low that you'd rely on the additional speed you gain from a flat structure, if there is any. It's not worth such an ugly hacky design
  • More flexible. You can add a 5th timeframe per value later and don't end up with empty columns
  • Less redundancy. (see following paragraph)

You should consider taking the Value names and addition value properties (unit, measurement device, measurement method, etc.) into a 3rd table and link both it with main model over the timeframe and the value. This is because the value name is a relatively huge string that is stored for every timeframe. Storing only a 4 byte integer (FK) is more efficient and flexible.

Improve this answer
4
  • 1
    +1. I agree. After writing my answer i reread yours, and I think we're saying the same things for the same reasons.
    – Sir Swears-a-lot
    Commented Apr 30, 2015 at 10:26
  • This sounds reasonable for me, waiting a bit before accepting. One more doubt: in this design trying to get full information from main model (+all timestamped stuff) would require one query to main and 30 or so to 'updates' table, followed by 30 or so to 'names' table. Can this really be fast?
    – zencodism
    Commented May 2, 2015 at 0:40
  • No it won't it's one query with multiple results. In MySQL Syntax: SELECT * FROM main JOIN frames ON frames.mainID = main.id JOIN values ON frames.valueID = values.id WHERE main.id = n Guess that should be it. Nothing special just a join with mutiple results. Also note that other design shemes like Snowflake or Star don't use the no redundancy approach but instead use a lot of tables (joins) for extra speed.
    – xuma202
    Commented May 2, 2015 at 10:10
  • 1
    Now I want to upvote xuma's comment. Thank you for an explanation, it was really simple thing I missed out (brainfart + little experience).
    – zencodism
    Commented May 2, 2015 at 13:19
0

I think the answer depends upon what you are trying to achieve and how the data will be used. Think more about the application design requirements.

From what you describe, I would recommend a single table with 1 key, datetime & value per row. ( I think this is what you describe in your 2nd option)

The reason I say this is this is that its easier insert the new 'now' record with today's date time. The newest record is current. Or you could choose to add an 'is_current' flag.

This approach would make it easier to store and maintain a rolling set of 4 values but doesn't stop you from having more or less values. It also doesn't force you to have fixed intervals. And it doesn't require you to update records to 'shift' records from one column to another if you have to maintain a rolling window.

To get the latest / current /now record you: select id, value, max (timestamp) From tab Group by id, value

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.