OT: design question

albruan posted on Wednesday, April 04, 2007

I'm having a problem deciding on the best way to tackle a design question.  My app has an editable root collection of Estimate objects that can be archived (removed from the database) and restored later should the need arise.  The Estimate objects reference drawing types that are contained in an editable root collection of Drawing Type objects.  The user is able to delete a Drawing Type object unless it is referenced by an Estimate.  The problem I'm having is how to handle the case if a user archives an Estimate object, later decides to delete a Drawing Type that was referenced by that Estimate, and, even later, decides to restore the Estimate object; the problem is the Estimate would now be referencing a non-existent Drawing Type.

I've thought of two possible solutions as follows:

1. The first one involves adding an additional field named something like Counter to the Drawing Types table in my database and then incrementing the field for each Drawing Type when it is referenced by an Estimate.  That way, the users wouldn't be allowed to delete a Drawing Type if its counter > 0.  The problem with this is if the user ends up archiving all Estimate objects that reference it and never has the need to restore those old Estimates; in that case, the Drawing Type could never be deleted.
2. The other solution would be to archive a copy of the Drawing Type along with the Estimate when it is being archived.  In that case, if the user later decides to restore the Estimate, the Drawing type would also be restored unless the Drawing Type still exists in the Drawing Types table.  The only problem I see with this is all the additional code that would need to be written to handle the solution.

Any ideas on which is the best method?  Or is there another method I haven't thought of?  Any response would be greatly appreciated.

Allen Anderson

hurcane replied on Wednesday, April 04, 2007

As a user, I would expect such a scenario to behave as your second description.

An alternative consideration is that you never physically delete drawings. Instead, give them a deleted status that can be set when they are deleted. That makes it very easy to "restore" them, as all you have to do is change a status.

Bayu replied on Thursday, April 05, 2007

I agree with hurcane's hints.

In addition there could be one other solution, but this depends on the functional scope of your restored Estimates.

Are your restored Estimates equivalent to 'original' Estimates in every sense? If so, then ignore my suggestion. However, if your restored Estimates are in fact 'read only' and only serve reporting/analysis and they can not be edited anymore, then perhaps consider making a EstimateHistory table. This table would have columns like your original Estimate table AND it would have columns for all relevant associated data (usually referenced through foreign keys on your original Estimate). When archiving you would copy the Estimate to the history table along with the values of those relevant associations.

Basically, you can then drop your restore option, since all relevant data is already in this history table and your archiving/reporting views could be served directly from this (e.g. using a HistoryEstimate object).

I used this approach in an Issue tracking system to maintain an archive of issues resolved in the past. The idea is also further described in the eBook that accompanies the Infragistics Tracker reference application.

Regards,
Bayu

albruan replied on Thursday, April 05, 2007

I kind of thought about doing what hurcane suggested, but the problem with it is that multiple Estimate objects can reference the same Drawing Type.

Whether or not the restored Estimates are to be read-only is something I need to find out from my client.  One thing they do want is the ability to create a new Estimate by cloning a previous Estimate and that may be the reason they want for being able to restore one.

I do have an EstimateHistory table in my db that gets filled via triggers whenever anyone adds, edits, or deletes an Estimate, but I don't have a means of using it for reporting since my EstimateHistory object is used only for reporting modifications that have been made to the Estimate; consequently, it has no way of knowing anything about the original Estimate's child objects as there are separate triggers on the tables containing them.

david.wendelken replied on Thursday, April 05, 2007

From a business standpoint, how should you handle the fact that a drawing type might change between the time it is archived and the time it is restored?

Drawing Type Versions?

Keep the current?

 

 

albruan replied on Thursday, April 05, 2007

Good question, David.  That's something that I haven't discussed with the client.  Thanks for bringing it up.

Allen