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
Best Answer
I'd recommend you make a date column to store all this data then partition and index it. You'll then be able to query it for days and months as you would usually query the date.
Here's some information on partitions (in terms of oracle DB): https://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.html
It effectively means if you partition by month and you only need to query that month you will only select data from that partition instead of the whole table.