Any suggestion for DB datatable design for this issue?

Question

It's a little complex. I'll try to be as clear as I can.

It's a work plan. We make estimation of the total time needed for a task, say 100 hours, and break it into periods of month. For example, 30 hours should be done in March, 20 hours in April, and 50 hours in May. After every month, the plan can be changed. I need to keep both the updated version and historical versions of the plan in DB, to see the trend and predict the future.

The sum of remaining hours of each task is important (to draw breakdown chart, you might already see that we're using scrum model). I'm not sure how to design it to be most flexible. And I plan to use Access 2003. I'm a little worrried about the performance, if the record number grows too fast.

Is there any suggestion for me? Thanks a lot.

Answer 1

I would design this with each set of monthly predictions handled as a group of rows. To support historical data add a "date created" field. So something like (this is T-SQL syntax, I gave up on Access a long time ago):

CREATE TABLE dbo.TaskEstimates
(
    DateCreated         datetime    NOT NULL,
    EstimateYearMonth   datetime    NOT NULL,
    HoursRequired       decimal     NOT NULL,

    CONSTRAINT PK_TaskEstimates PRIMARY KEY CLUSTERED
    (
        DateCreated     ASC,
        EstimateMonth   ASC
    )
);

The DateCreated field would be the same for all members of an estimate set entered today, for example. The EstimateYearMonth field contains just the year and month (day = 1, time portion = 0:00:00.000).

Combinations of aggregate functions and grouping criteria then allow retrieval of any combination of data you need.

In addition to, or instead of the DateCreated field you could also use a GroupID that is incremented for each set. So the first set all has GroupID == 1, the next set == 2, and so on. This might be easier for historical selection if you're asked counted-query questions instead of dated-query questions; e.g. "give me the 3rd cycle estimates" instead of "give me the estimates done in June".

BTW, I normally use datetime for all date and time related data, instead of separating it into year, month, etc. so I only need to normalize a few selection criteria instead of fiddling around with type conversions for each row.

Answer 2

TaskMaster
   tid
   datecreated
   estimatehours

TaskBreak
   tid
   dateupdate
   month
   hours

here, in TaskBreak (tid,dateupdate) is primary key. When you update your task hours make a new entry in TaskBreak table. So this way you will have both updated versions and original version of task.
If you are updating task plan once only in a month then you can store just month and year in dateupdate column of TaskBreak table.