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.