Database Schema for Organizing Historical Stock Data

Implement database schema for organizing historical stock data

(It's not a "stupid problem", just a "novice question".)

PRIMARY KEY(ts_code, trade_date)
INDEX(trade_date)

But have trade_date DATE (not INT)

DECIMAL(6,2) limits you to 9999.99; is that OK?

Use ENGINE=InnoDB

Be cautious of other Questions that are not tagged [mysql] or [mariadb]; they are likely to have syntax and other suggestions that are not good for MySQL.

If you include "time", it is probably better to use a single DATETIME column, not two columns (DATE and TIME). However, this leads to some tricky business when requesting info for a given date.

Database schema for organizing historical stock data

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.

How to properly organize historical data in the same table?

Assuming

• A competition is composed of 1 or more rounds,
• A round is optionally composed of 1 or more groups.

Then I recommend

• One table containing one row per 'competition'.
• One table containing one row per 'round'. It should contain a competition_id that is a FK to competition.id.
• One table containing one row per 'group'. It should contain a round_id that is a FK to round.id.

(Etc.)

Those are examples of doing "1:many" mappings. (Note "0 or more" and "optionally" are merely edge cases of "1:many", and do not require extra effort.)

I say "one table" because "vertical splitting" is rarely unnecessary. Simply put all the attributes for a "competition" in a single table. When some attribute (such as the 'rounds') is repeated, then it cannot be put in the same table.

(The table name competition_rounds, though descriptive, was confusing me.)

A related question... Are all the 'rounds' of a 'competition' played in a single country? I see country_id in competition; I wonder if it should be moved to rounds?

MySQL database organization for stocks

Simply said: No. THough you may want to add the colume to the historical prices.

What you may also want is to have a market table and to use lookup tables for industry, sector, possibly prediction - which should possibly be (the prediction) in a separate table with... a date (so you can look back to past predictions).

---

Related Topics