用于组织历史股票数据的数据库模式 [英] Database schema for organizing historical stock data

问题描述

我正在创建一个用于存储历史股票数据的数据库架构.我目前有一个架构，如下所示.

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

我的要求是存储多个股票代码的柱状数据"(日期、开盘价、最高价、最低价、收盘量).每个交易品种也可能有多个时间范围(例如 Google 每周条形图和 Google 每日条形图).

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).

我当前的架构将大部分数据放在 OHLCV 表中.我远非数据库专家，我很好奇这是否太天真.非常欢迎有建设性的意见.

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);

这意味着我的查询目前类似于:查找给定交易品种/时间帧的时间帧 ID，然后在时间帧 ID 匹配的 OHLCV 表上进行选择.

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.

推荐答案

好吧，从积极的方面来说，您应该先征求意见.这使您领先于 90% 不熟悉数据库设计的人.

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.

• 没有明确的外键关系.我认为 timeframeID 与 symbolID 相关吗?
• 目前还不清楚您如何通过这种方式找到任何东西.阅读上述外键应该可以毫不费力地极大地提高您的理解.
• 您将时间范围数据存储为 TEXT.从性能和可用性的角度来看，这是一个禁忌.
• 您当前的计划无法适应股票拆分，而这种拆分最终会发生.最好在价格数据表和 Symbol 之间再增加一层间接性
• open、high、low、close 价格最好存储为十进制或货币类型，或者，优选地，作为具有存储除数的单独 INTEGER 字段的 INTEGER 字段，因为允许的最小价格分数(美分、8 美元等)因每次交易而异.
• 既然你支持多种交易所，你应该支持多种货币.

• 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.

这篇关于用于组织历史股票数据的数据库模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！