Pre-populate capture records for one session.

Question

I have capture screen where a user will capture daily production figures for several performance categories, i.e. they will enter a single figure per category on any particular day.

Now I don't want the user to insert a new record for each category-figure record, so I plan to check if the day has records for all categories. If not, before opening the capture screen, I will add a record for each category to that day.

One problem is that over time, categories can be added, so if I have 30 categories, and check if Oct 12 has 30 categories, it could be that on Oct 12 there were only 27 categories. I will then need a CreatedDate on the categories, and when I check categories for a particular day, I will only count categories created on or before that particular date.

Any other means of capturing daily figures besides my capture screen will be prohibited, so I am fairly confident that if only a sub-set of categories exists for any given day, they were not properly created, and I can delete them and recreate all categories for that day.

EDIT: I can no longer do the above, as the user may capture data for one day, and in the same day decide to add a category. Using only categories with a create date earlier than the current date will prevent the new category being visible today. Not using the 'earlier date' method will result in today's data being cleared.

Is this design sound? Can I do anything to improve it?

Answer 1

Is it not possible to store a list of currently-active categories in another table? This sounds like the sort of information that should be explicit rather than inferred from previous days.

Answer 2

When I see C(reate) and R(ead) I immediately think about U(pdate) and D(elete). Can categories also be removed? If so then you need both a start date and an end-date.