CODEWITHSUNDEEP
sql-serverdatabase-design
jaks
jaks

Reputation: 4587

Stock price History Database Design

I need to store the stock price history. Below are the requirements,

  1. First 30 days hourly data should available
  2. For 10 years history has to be maintained, which should contain daily high & low

There are multiple view types,

The table design is

HourlyStockPrice

symbol
datetime
price

DayStockPrice

symbol
date
high
low

MonthStockPrice

symbol
date
high
low

Since the database will have millions of record, I decided to store information in separate tables, so retrieval will be faster. Is this fine? or I need to go with only single table and based on need, the data has to be queried and processed. Below is single table schema,

StockPrice

symbol
datetime
high
low

Please advise.

Upvotes: 3

Views: 4847

Answers (1)

Hoots
Hoots

Reputation: 1976

You haven't mentioned how these tables get populates. My solution would be to have a trigger on the main stock price table that populates a single history table with the datetime of insert and updates with before and after prices.

You should be able to do all the rest with indexed views on the history table breaking down the updated (datetime) field into hours, days, months.

In case you're not familiar with indexed views they are simply views that you can index. The index data is kept with the view instead of the table.

The Hour Example may be something like...

CREATE VIEW ivHourlyStockPrice
WITH SCHEMABINDING
AS
BEGIN
     SELECT symbol, convert(varchar(255),updated,106) as Day, select convert(char(2), updated,108) as Hour, AfterPrice as CurrentPrice
     FROM StockHistory
END

CREATE UNIQUE CLUSTERED INDEX idx_ivHourlyStockPrice ON ivHourlyStockPrice(symbol,Day,Hour,CurrentPrice)

Then doing something like...

select Day, Hour, CurrentPrice
from ivHourlyStockPrice
where Day='03 Jun 2014'
  and symbol='xxx'

would be extremely fast.

Upvotes: 1

Related Questions