As per our discussion, a bug was found when a subassembly was edited in the system. Rather than the Task To Assembly table referring to the original version of the Assembly (as at the time it was created), it refers to the modified version of the subassembly.
We determined the best way to fix this would be to create a clone of an entire assembly, each time any record within that assembly was changed, i.e. the following tables: Assembly, Subassembly, AssemblyToAssemblyGroup, AssemblyToMaterialGroup, AssemblyToMaterial. The clone would become the new record and the previous version would be marked as deleted. Note 1: To do this, we would need to change the way items deleted - possibly changing deleted to a timestamp. We would also need to use this timestamp as part of the unique constraint for the records in the above tables. Note 2: To avoid more data than necessary, only assemblies with records referring to them in the TaskToAssembly or TaskToMaterial tables would be cloned, otherwise the record would just be updated.
As per our discussion, a bug was found when a subassembly was edited in the system. Rather than the Task To Assembly table referring to the original version of the Assembly (as at the time it was created), it refers to the modified version of the subassembly.
We determined the best way to fix this would be to create a clone of an entire assembly, each time any record within that assembly was changed, i.e. the following tables: Assembly, Subassembly, AssemblyToAssemblyGroup, AssemblyToMaterialGroup, AssemblyToMaterial. The clone would become the new record and the previous version would be marked as deleted. Note 1: To do this, we would need to change the way items deleted - possibly changing deleted to a timestamp. We would also need to use this timestamp as part of the unique constraint for the records in the above tables. Note 2: To avoid more data than necessary, only assemblies with records referring to them in the TaskToAssembly or TaskToMaterial tables would be cloned, otherwise the record would just be updated.