Currently we have a huge database from which we are aggregating necessary information for a project to be stored in a database specific to that project.
A new functionality now requires data to be fetched based on a time scale which can be - days, weeks, months, years. We are open to database redesign in order to optimize the queries.
The only solution I can think of is -
Have table with entries for days called day table, once it hits the 7day mark update a week table entry and once that hits a limit update the month table and so on. So when we have the query for 2010-2017 we'll fetch 2010-2016 from the year table then the months from the month table and the days from the day table.
The possible difficulty I can see at the moment with this proposed solution is that it would be difficult to calculate week ranges.
Is there a better way to solve this problem or any suggestions that can improve the solution proposed.
Any help is appreciated.I'm new to working with Database design.
Example : From 2015-2017(till 06-Apr-17) Then - Get values for 2015-2016 from the years table Jan 2017 - Mar 2017 from the months table 01 Apr 2017 - 06 April 2017 from the day table