I would like to download stock market data from the internet (for example by scraping…) and organize them in a database (I am using python and SQL) which updates daily or on request. (The idea is to create a screener, do back testing and so on…) I am not very familiar with databases, so I am looking for some suggestions regarding the database structure.
The structure I have in mind is the following (see picture).
There are 8 tables (TICKER LIST, OHLCV and 6 different tables for the financial statements).
The main table is TICKER LIST: it should contain some sort of unique ID of the securities. I think the ticker symbol cannot be a unique ID, become ticker symbols for a company can change. The company name cannot be a unique ID become it is possible to have the same company on different exchanges. This is why I created the column ‘id’.
At each ‘id’ is associated an OHLCV table. For example BP will have a OHLCV table in USD from the NYSE exchange and a OHLC table in GBP from the LSE exchange). However the financial statements (Income, Balance, Cashflow…) should be the same for both of exchanges. This means I will have 2 replicates of the financial statements tables exactly identical, one for the id 987448 and the other for the id 239484 in this example. Maybe having replicates is not the best way to go…
My question is, does this structure make sense? How would you improve it? As I said I am not familiar with databases. Thanks