Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need some help determining how to best design my tables. I have about 200GB worth of data I wish to place in a table (historical option price data). The table looks something like:

String symbol
Date date
Date exp;
double strike;
double ask;
double bid;
<20 other columns>

95% of the time I will be fetching the current date's data. But the other 5% of the time I want to analyze historical data.

Most of the queries would involve some combination of symbol/date/exp.

I'm not sure how to best organize the table/s in terms of efficiency. Would it be best to put the data into two tables: historical and current? Or an index on the date? Or maybe an extra boolean column (isCurrent) which indicates which data is current or not?

If it matters, I'm using java/hibernate/mysql under the covers.

Thanks

share|improve this question
    
I'm tagging this with MySQL because you're really asking (or should really be asking) about how best to exploit RDBMS-specific features for your use case. It's not strictly a table/database design question. –  Jon Seigel Apr 14 '13 at 15:54
add comment

1 Answer

up vote 2 down vote accepted

The best first strategy is always to keep your transactional data all together in one, normalized data store. This minimizes the risk of data integrity problems and keeps the amount of code you have to write to a minimum.

Often, people will find that there is some kind of concern around performance. This leads them to consider denormalization. The best thing is to wait until your volume testing proves that there is a real problem with performance, rather than assuming that there will be one because your table is "big".

Given that you have a large amount of historical data that you don't use most of the time, it is possible that you may benefit from denormalization. You really should test this out before assuming it to be the case though. If it is the case, then one approach which might work well for you is to keep two data stores: (a) one where your current values are kept and (b) one where all values, including current and historical are kept.

If this makes sense to do (for performance reasons), you should build some automation to track all changes to the current data and record them in your history table. This is best done with triggers in DBMS that support them.

What almost never makes sense is keeping current values in one place and historical values (excluding current values) in another place. I have never come across a situation where you need to query only historical records, excluding current values. This means that your queries would have to be twice as complex in order to use current + historical values if you segregate them in this way.

This is what I would recommend to you:

  • Keep you data in one place if you can (definitely index on date)
  • Denormalize if you have to, but keep current values in your history table.
  • Do not use a bit flag for "is_current". This almost never indexes well.
share|improve this answer
    
I think this covers things well, though moving historical data to a separate table isn't necessarily denormalization unless this removes non-prime dependencies. Also I wouldn't reject an "is_current" flag out of hand - I've seen cases where this is very effective. –  Chris Saxon Apr 14 '13 at 15:53
    
Note: I've retagged the question with [mysql] for the reasons I mentioned in my comment. –  Jon Seigel Apr 14 '13 at 16:00
    
Thanks for the response. Given that it takes a long time to chew threw the data (about 1.5 days to push the data), I didn't want to test first but see best practices. I'll follow your guide here and see how it goes. –  dbh21 Apr 14 '13 at 17:24
    
@ChrisSaxon - Quite so, it isn't denormalization technically. I sometimes fall into the trap of using the term in a less correct way as many others seem to do. The term fits (loosely) insofar as it involves deliberately introducing data redundancy for the purpose of improving performance. I think the problem is that we don't have a good jargon term for "keeping an extra copy of something for performance purposes" as opposed to the more technical definition of denormalization (copying non-key fields from parent tables to child tables). –  Joel Brown Apr 14 '13 at 18:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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