0

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

Improve this question
2
  • What is the advantage (in your opinion) of moving the data around between tables? If seven days worth of data is moved from the days table to the week table, will anything be deleted? Could you elaborate on that point? The explanation of fetching data should be expanded to include more examples. What happens if I select data from 2015-2016? What exact dates would be used to fetch the data? 2015-01-01 to 31-12-2016?
    – John K. N.
    Commented Apr 6, 2017 at 9:59
  • 7 days worth of data if moved will not result in any deletion but would help faster fetches if the time scale chosen is weeks. The data for the weeks will be accumulated in weeks at one place so retrieving it and then processing it would be easier. This was my reasoning behind the solution. if 2015-2016 is chosen then the 2016 year table entry will be chosen which has the entry for that year(from 1st Jan to 31st Dec) @hot2use
    – Aishwarya
    Commented Apr 6, 2017 at 10:24

1 Answer 1

Reset to default
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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