I'm looking for any advice on how to efficiently store some resourcing data for our app.
For each project in our system we need to store the resources required and this comes in the following form:
We store: the stage (and if needed the task) that the resource is associated to, the role they are undertaking, the date period and then either a number of hours per day, days per week or percentage of their time over the date period to be allocated to that user.
Now this may all seem pretty straightforward so far but where the complication lies is with how the data is to be displayed. Users will be able to see the resource plan for a project either by day, week or month. They will also be able to edit the data in any view (we have a textbox per day/week/month). So the data may have initially been entered with a date range or 2015-01-01 to 2015-12-31 but this could be edited in the week view allowing a user to change just one week in the middle of this range to a different allocation percentage. This would mean we would essentially have 3 entries once the update was done.
We have toyed with having an entry per day no matter what the period is they enter it in as this would stop us having to split existing entries when changes are made, but that will mean we will end up with millions and millions of rows of data and this is what we are trying to avoid.
At present it would seem our options are to either have massive amounts of data or have to deal with the complications of splitting existing entries when updates are done using a different date view to what the resource information was originally entered in.