1

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!

1

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 !

  • Consider that the cities are approximately 8.000 and for each city there are at least 4 global statistics entries. You said that you would add a new column on the Statistics_Entry table, but doing so I will need to duplicate the statistics_entries for each year, I mean I am going to have a Statistics_Entry table with : "public education"->"2013", "administration"->"2013" etc..., - and then again with another year - "public education"->"2014",etc.. Maybe did you intend that you would add a 'year' field to the Statistics lookUp table as I was thought? Or am I mistaking something? – user3019105 Mar 7 '14 at 12:33
  • Well your look up table is a result of your statistics_entries and Cities ? correct ! where from would you take your year column ? if it's not present on these two tables ? – Up_One Mar 7 '14 at 12:40
  • Yes, I was thinking about saving the year and the percentage amount of the statistics entry in the look up table, what do you think? Doing so when the user selects a city, a year and types his taxes amount, I only need to bind all the 3 tables together thanks to the lookUp table's Primary Key and then filter with WHERE clause the records where the year is YYYY, as selected form the users. Could this be a possible solutions? – user3019105 Mar 7 '14 at 12:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.