I'm hoping for advice on designing a workorder system that has to support recurring workorders. For example, An administrator needs to be able to set a workorder to repeat in a unit of time like every 6-months.
I'm using NodeJS as a server, a noSQL database (couchDB) for storage and elasticsearch as a search engine. Here's my first attempt at a document structure to support this:
Base Workorder
{
_id: 1
... Unrelated fields
"recurInValue" : 6,
"recurInUnit" : "months"
"lastRecurredOn": 1368914552527,
"numOcurrences": 1,
"numTimesToRepeat": null,
"stopOn" : 1384920941195,
"createdAt" 1364936177618
}
Occurrence Workorder
{
_id: 2
... Unrelated fields
"recurFrom" : 1
}
Occurrence Workorder
{
_id: 3
... Unrelated fields
"recurFrom" : 1
}
My current CRUD plan is:
A cron job will poll the database hourly and get all of the workorders that have a non-empty recurInValue (base workorders). It will then calculate the actual date of recurrence by adding the time given by recurInValue and recurInUnit to lastRecurredOn, if present, or createdAt, if not. moment.js works great for this. If the actual date of recurrence is <= now, the system will copy the workorder, set the recurFrom property to the base workorder's ID, and update the appropriate fields.
This one is a little tricky as workorders have associated notes, activity log entries, and system log entries. These associated items all live in their own documents that have a workorderId property set to the parent workorder's ID. Locating an individual workorder's associated documents is trivial and is done by couchdb.
However, using notes as an example, the customer wants to see all of the notes from past workorders along with an occurrence workorder's notes. I've got this working by using ElasticSearch to find all of the workorderIDs that either share the same recurFrom value (id of base workorder) or that have an _id that matches the workorder's recurFrom property (base workorder). The system then bulk fetches the corresponding notes in createdAt order (chronological) and passes them off to the view.
I haven't worked out how to display everything though. A user could in theory add a note to a previous workorder after the latest note on the one the user is viewing so displaying the notes in chronological order may be confusing. I might be able to get around this in the view by differentiating the current workorder's notes from other notes by color or in separate sections, but I'm not sure about that.
One thing that seems tricky here is updating the recurInValue. If that changes, I think I would need to change the base workorder, not the occurrence workorder, so for example, if the user clears out the recurInValue the workorder will stop repeating.
I'm still a little fuzzy on this. If the user wants to delete an occurrence workorder, I can just delete it and its associated documents and ask them if they want to stop the workorder from repeating. If they say yes, I can clear out the base workorder's recurInValue.
If the user deletes a base workorder, repeating will obviously stop but all of the occurrence workorders will have a recurFrom property that points to a non-existent document. If I leave the recurFrom property as is, I will be able to load all occurrence workorer's associated documents and display them properly, but it doesn't feel right.
I'm not tied to this implementation, it's just what I've come up with so far. I considered a linked list implementation where each occurrence workorder would point back to the previous workorder, but it seems having to 'walk the list' in a long chain of workorders and load each workorder's associated documents individually would result in an excessive number of queries and not be very efficient.
Any advice you can give me is appreciated!