Design help: complex business object structure

SonOfPirate posted on Friday, December 03, 2010

I could use a little help wrapping my head around the approach I should use to model a specific requirement a set of recurring activities that are used by my application.  Think of it as a Preventative Maintenance schedule.

Each item in my system is assigned one of a set of PM schedules.  Each schedule defines how often the PM must occur and how much time the tech has to complete the work.

Essentially, I have one database table that lists the various schedules along with their recurrence pattern and how long the tech has to complete the work once is is started.  Another table contains a record for the specific occurance along with additional information that is entered and editted once the activity has been started.  For purposes of this discussion, let's assume the tables are in different databases (cuz they are!)

When the user opens the application, I need to present them with a list of "current" activities.  Current means that they have either already been started and remain open or have not been started but are due.

My initial impression is that I will need to follow these steps:

1. Query the metadata table for the list of schedules and rules.
2. Lookup any items that has PM due.  This means applying the recurrence rules to the item's last PM date to see if the PM was due on or before today and is still editable.
3. For each item in need of PM, I look to see if there is already a record in the instance table.
   1. If so, then I display the activity in the Open state (meaning the user can edit the existing record)
   2. Otherwise, I display the activity in the Not Started state (meaning that a new record will be created)

There is a separate use case for an exception report that managers will use to view all PM instances that remain open after the edit window has passed.  In this case, I am filtering those records out so that the tech can only edit/create instances for items that are due and still editable.

My problem is that I can make it work but if feels very hack-ish.  I'm wondering how you would approach setting up the business objects and managing the interaction required to present the data to the user.

Thanks in advance.