Mysql – How to organize the tables of a MySQL database with several statistics data of several years

database-designMySQLstatisticstable

I am developing an app that must provide statistics information about the distribution of the money that a person spends each year in taxes in the city where he lives. The user should select the interested city, a year for which the statistics data will be recovered and provide the amount he spent in taxes.

Then it should see for example that if he spent 4.000 dollars in taxes, the 45% of that amount (1800 dollars) was invested by the city for public education, the 20% (800 dollars) was invested in the improvement of public infrastructure, the 10% was invested in culture and the 25% was invested in administration.

I am using a MySQL database and my database structure is like follows:

  • I have one table 'Cities' containing the names of all the concerned cities of the statistics;
  • Another table 'Statistics_Entry' containing all the statistics entry names that will be used (for example, "public education", "administration", "public infrastructure", and so on…;
  • A lookup table 'Statistics' which binds the two tables explained before and contains the percentage of each Cities<->Statistics_Entry association (the primary key of the lookUp table consists of two INT fields, which corresponds respectively to the INT ID primary key column of the 'Cities' table and the INT ID primary key column of the 'Statistics_Entry' table.

So here is a simple explanaition of the db structure. But here comes the need of having different statistics results for different years.

At a first look I thought of using another column in the lookUp table named "year" which gives the proper year of the statistics data. But I am not sure if this is the best way.

So I am asking some advices on how structurizing this kind of information system.
Should I create another table 'Years' which will contain all the years of the statistics or is it better to store the years in a single field inside the 'Statistics' lookUp table?

Is the db structure I showed good or bad? What could be improved?

Thanks! Hope for some help!

Best Answer

Well , i would add a new column on the Statistics_Entry as your year column !
Next depending on the volume of your data i would partition that table using this column(this will speedup your query when using the year column as your predicate).
This lookup table can be a view !
I would not create a new table just for the year column, this would just make thing complicated ! in my opinion !

Related Question