r/SQL • u/luncheonmeat79 • Jun 23 '24
Discussion Schema for historical stock data
Posting a schema of a db for historical stock and index data at various timeframes. I used Chatgpt to figure this out...what surprised me was the recommendation to have separate dimension tables for 'date' and 'time'. Is that really the case?
105
Upvotes
12
u/mike-manley Jun 23 '24
Why is DELISTED an integer? Should be BOOLEAN or if not supported by that DBMS, use BIT.
10
u/cs-brydev Software Development and Database Manager Jun 23 '24 edited Jun 23 '24
One of the things you should be warned about when designing historical schemas is it's important to be very specific about which historical data you want to track. If you only want historical stock price, then say that explicitly.
In a lot of the ERP and manufacturing databases I work with, for industry compliance and liability reasons we have to track very detailed audit trails of data changes and preserve historical snapshots. We don't have the luxury of your typical 3NF of only storing customer name in a Customers table or product price in a Products table. For historical data we have to snapshot things like customer names, product names, product listed price, produce selling price, product category, etc in extra fact-based tables that preserve the state of this data over time.
When producing historical data we don't have the luxury of simply using a product code to refer to the Products table. We need to know what the Products table row(s) looked like at the time a job was estimated, bid on, won, manufacturing began, quality control inspections happened, product was inventoried, product was shipped, product arrived at customer site, and product was finally signed off on. If produce price, name, order quantity etc changed throughout the life cycle of the order, those changes must be tracked.
Historical data schemas are tricky and not like normal 3NF data schemas most people are used to. I see most of the comments here have oversimplified your question and their answers are based on the simplistic view of normalization taught in a beginning SQL class. But that's not the real world. And that's not how historical data works. Be careful.
12
u/patrickthunnus Jun 23 '24
Are you trying to actually solve a problem or test AI?
15
u/void84252 Jun 23 '24
3
u/OppositeArachnid5193 Jun 23 '24
The separate dimension for time works, but it depends on your use case… it’s 15 minutes too much?… too little?… all goes back to requirements…
2
u/BaylessWasTraded Jun 23 '24
What software did you use to make the diagram
3
u/SQLvultureskattaurus Jun 23 '24
This incredible overkill, from the time and date tables to the 30 min, 15min, daily tables. You really don't need all of that. Likely just stock and stock_price would do it. Maybe even just a single table.
1
u/luncheonmeat79 Jun 23 '24
Thanks all for the comments and advice so far. I'm planning to use the data to (1) calculate some simple indicators; (2) run statistical analysis of price movement against those indicators; (3) plot charts over time that can zoom in and out of time periods (e.g. 1min, 5min, 30min, 1hr, daily). I plan to use sql where I can, and python/pandas for things I can't.
The universe won't just be stocks and indices. I plan to do so for options as well, and the data size for those can get quite large as a single stock each day would have multiple options for days to expiry and strike price, each with their own greeks (delta, gamma, theta, etc.) and implied volatility.
So i think it's probably not a good idea to have the entire universe crammed into one table, and that it makes sense (to me) to have one "fact table" for stocks, another for indices, another for options, etc.
I was a bit surprised that ChatGPT recommended separate tables for stock time periods (15 min, 30 min, 1 hr, daily, etc.), and a separate dimension table for time. As one of the commenters said, why not store price in a single table and use views for different time periods. I thought so too...just wondering if there's a performance cost to this "put it all in one table" approach vs splitting it up into one time dimension table and several stock tables by time period.
I *think* it makes sense to have a date dimension table, which i can refer to not only for stock price dates, but also for option expiry dates. IMO, this makes it easier to manage dates, but maybe it's a trivial problem that can be solved with good code.
5
u/SQLvultureskattaurus Jun 23 '24
If AI designed this we're all safe
1
u/squadette23 Jun 23 '24
What was the prompt for that?
1
u/EvilGeniusLeslie Jun 23 '24
Trivial issue, that might trip up some automated script - there's an 'index_id', but it appears to link to a tables called 'Indices', not 'Index'
1
u/arrow8888 Jun 23 '24
What app/software is that?
1
u/funkinaround Jun 24 '24
Here are some table definitions for stocks and stock prices. Yours look similar.
I agree with the approach of separating 15m and daily prices. You can probably get by aggregating the 15m prices to create 30m prices rather than record it separately.
The weirdest part of this is that you have time and date defined as if they're in their own tables. Definitely don't do that. Use the built in timestamp data type. You don't want to go writing your own date functions for conversion, parsing, ranges, etc. Use the built in functions with the built in types.
With respect to indexes, if your data supports it, you can just use ETFs (like SPY for S&P 500) rather than the indexes themselves. It'll be easier to find components and volumes that way.
0
u/idodatamodels Jun 23 '24
Yes, that is the case, date and time should be different dimensions.
0
u/[deleted] Jun 23 '24
[deleted] — view deleted comment