Database schema for organizing historical stock data

Question

I'm creating a database schema for storing historical stock data. I currently have a schema as show below.

My requirements are to store "bar data" (date, open, high, low, close volume) for multiple stock symbols. Each symbol might also have multiple timeframes (e.g. Google Weekly bars and Google Daily bars).

My current schema puts the bulk of the data is in the OHLCV table. I'm far from a database expert and am curious if this is too naive. Constructive input is very welcome.

CREATE TABLE Exchange (exchange TEXT UNIQUE NOT NULL);

CREATE TABLE Symbol (symbol TEXT UNIQUE NOT NULL, exchangeID INTEGER NOT NULL);

CREATE TABLE Timeframe (timeframe TEXT NOT NULL, symbolID INTEGER NOT NULL);

CREATE TABLE OHLCV (date TEXT NOT NULL CHECK (date LIKE '____-__-__ __:__:__'),
    open REAL NOT NULL,
    high REAL NOT NULL,
    low REAL NOT NULL,
    close REAL NOT NULL,
    volume INTEGER NOT NULL,
    timeframeID INTEGER NOT NULL);

This means my queries currently go something like: Find the timeframeID for a given symbol/timeframe, then do a select on the OHLCV table where the timeframeID matches.

Answer 1

We tried to find a proper database structure for storing large amount of data for a long time. The solution below is the result of more than 6 years of experience. It is now working flawlessly for our quantitative analysis.

We have been able to store hundreds of gigabytes of intraday and daily data using this scheme in SQL Server:

 Symbol -  char 6
 Date -  date
 Time -  time
 Open -  decimal 18, 4
 High -  decimal 18, 4
 Low -  decimal 18, 4
 Close -  decimal 18, 4
 Volume -  int

All trading instruments are stored in a single table. We also have a clustered index on symbol, date and time columns.

For daily data, we have a separate table and do not use the Time column. Volume datatype is also bigint instead of int.

The performance? We can get data out of the server in a matter of milliseconds. Remember, the database size is almost 1 terabyte.

We purchased all of our historical market data from the Kibot web site: http://www.kibot.com/

Answer 2

Well, on the positive side, you have the good sense to ask for input first. That puts you ahead of 90% of people unfamiliar with database design.

• There are no clear foreign key relationships. I take it timeframeID relates to symbolID?
• It's unclear how you'd be able to find anything this way. Reading up on abovementioned foreign keys should improve your understanding tremendously with little effort.
• You're storing timeframe data as TEXT. From a performance as well as a usability perspective, that's a no-no.
• Your current scheme can't accommodate stock splits, which will happen eventually. It's better to add one further layer of indirection between the price data table and the Symbol
• open, high, low, close prices are better stored as decimal or currency types, or, preferably, as an INTEGER field with a separate INTEGER field storing the divisor, as the smallest price fraction (cents, eights of a dollar, etc.) allowed varies per exchange.
• Since you support multiple exchanges, you should support multiple currencies.

I apologise if all of this doesn't seem too 'constructive', especially since I'm too sleepy right now to suggest a more usable alternative. I hope the above is enough to set you on your way.

Answer 3

I'm not sure what value is added by Timeframe - it seems like an unnecessary complication, but that could be something I'm failing to understand ;-) Can a Timeframe have more than one OHLCV? If not, then I'd suggest they be merged.

I would note also that stock tickers change from time to time for any number of reasons. It's not a frequent event, but it happens. If you're thinking about working with your data as time series, you should be aware of the issue so that you can handle it when it comes, if not before. If you're not tracking stocks (you may be working on a futures app, say) then this advice may be taken with the appropriate amount of salt.

Again mostly relevant to stocks, splits have been mentioned elsewhere and you may want to consider dividends - a stock's price will typically fall by the dividend amount (or more accurately the present value thereof) on the ex-dividend date, which may be misinterpreted if you don't know a confirmed future cash flow was the reason. Rights issues can be fun, too.

If you're planning on looking at series of data for a particular symbol, I'd suggest looking into what sort of performance you're going to get. At the very least, make sure you have an appropriate index in place.