I'm in the process of designing a MySQL database (InnoDB) that holds a large amount of economic data which our application scores. The size of the economic data varies a great deal: some measures, such as percentage changes, are fractions of a percent; others, such as national debt figures, are 14+ digits long. Furthermore, we have a business requirement that states some data points need to be correct to the 6th decimal place.
We currently have over 500,000 rows in the legacy database, but anticipate growing that number substantially in the future, as we are building the new database with point-in-time considerations and, for the most part, will not be deleting or updating rows, only adding new rows and superseding the old rows.
All potential tables containing this economic data will be structured as follows:
id | country_id | period_id | [economic_data] | data_type | date_created | date_superseded
My question is this:
Is it best to :
- Break out all of these individual economic data series into their own tables, given that the data comes in varying sizes, or
- Combine all this data into one massive table, given the identical structure of all economic data tables and the simplicity it would offer for writing queries?
We collect over 200 data series and are planning on increasing that number every year, so option 1 would require creating and maintaining 200+ tables.
Option 2 seems the easiest to develop and maintain, but I wonder what the implications might be on query performance and storage.
Any thoughts or suggestions?