My company is starting a new initiative aimed at building a financial database from scratch.
We would be using it in these ways:
- Time series analysis of: a company's financial data (ex: IBM's total fixed assets over time), aggregations (ex: total fixed assets for the materials sector over time), etc.
- Single company snapshot: various data points of a single company
- Analysis of multiple companies across multiple data fields for a single time frame, usually the current day.
- Back-testing, rank analysis, data analysis, etc. of ideas and custom factors.
Approximate breadth of data:
- 3000 companies
- 3500 data fields (ex: total fixed assets, earnings, etc.)
- 500 aggregation levels
Periodicity: daily, monthly, quarterly, annual
20 year look-back that would grow over time
Question: In our PostgreSQL database, what schema should we use? Right now I am thinking one time series table per company, per category of data field for the fully normalized DB. For example, one table for, say, all the balance sheet fields for IBM, another table for IBM's cash flow items, etc for all categories of data and for each company. Timestamps as records and data fields as columns/fields. Then for fast queries, create a warehouse and views, etc. that are not fully normalized but optimized for queries for my use-cases listed above. However, if you look at my number of companies and fields above, I will probably end up with more than 200,000 tables for just my base financial data if my tables are pretty wide, which isn't great either. That's a lot of tables, but I don't see another good way to do it.
If there is a better place to ask this question, please let me know.
If you need more information, I am happy to edit my question and add it.
PS - I asked a similar question on the SO Quant site, but didn't get much schema help. Also, non-schema focused answers are okay, but note that I am looking for help with schema design here.