I am creating a database table for storing numeric time-series data sets regarding cities that will be accessed via a web app. Primarily annual, quarterly and monthly data will be stored in it. There will be hundreds, potentially a couple thousand data sets, for each city. Some data sets may have data on a monthly, quarterly AND annual basis while other data sets may only be available on a monthly, quarterly OR annual basis.
I am trying to decide between 2 different designs for the table's columns:
With Option #1, a hypothetical row look like:
12345 _ 2012 _ 'Q4' _ 'Property Taxes Collected' _ 950000 _ 'USD' _ 'city govt'
OR with monthly data:
12345 _ 2012 _ 'M8' _ 'Property Taxes Collected' _ 330000 _ 'USD' _ 'city govt'
With Option #2, a hypothetical row look like:
12345 _ 2012 _ 'Q4' _ 950000 _ 1400500 _ 234.53 ... XXXX
OR with monthly data:
12345 _ 2012 _ 'M8' _ 330000 _ 1400500 _ 120.12 ... XXXX
If I do #1, I will have hundreds (or maybe a couple thousand?) of columns and many rows will have a lot of 'blank' spaces because a lot of data sets will not have data for each row (e.g. some data that is only available quarterly will always be 'blank' in columns for rows describing monthly data). Also, I think there will need to be a corresponding table for describing the sources and units (e.g. U.S. Dollars, Square Miles, etc.) for each dataset column. I am concerned that the large number of columns and blank spaces may be problematic.
Alternatively, if I do #2, I will have very few columns but millions (potentially billions?) of rows and will have many queries with a
WHERE clause searching to see if the
dataset_name column equals a certain string (e.g.
SELECT * WHERE dataset_name='Property_Taxes_Collected'). Also, since all types of numeric values are stored in the
dataset_value column, the data type here will have to be pretty flexible (Maybe something like
DECIMAL 16,4?). I'm primarily concerned this may become slow with a lot of data.
I am using MySQL.
My Question: Is possible to say which of these 2 general table designs is most likely the better design choice if my primary concern is query speed? Or is it fairly clear that I should be approaching this in a completely different manner?