Databases and Deep Copy

Question

If I find myself wanting to do a deep copy of an object stored in my relational database, have I necessarily done something fundamentally wrong architecturally? This is a different angle on another (much more detailed) question I asked, but haven't gotten much response to called Copying Relational Table Data.

Answer 1

Not necessarily. I have done this myself with much success to implement a versioning scheme. Basically entire graphs could be versioned (using a compite key, where one part of the key was the thing id and the other was the version number), and we would have access to all previous version of the graph or sub graphs easily.

To be clear, the DB Architect recommended this scheme; his opinion had a lot of weight, and the solution wasn't my first choice. But in the end it worked really well.

Answer 2

Normally if you copy the parent object you would only want to copy the reference to the child object, not the object itself. However, there are cases, like preserving the state of an object at a given point in time, where copying the child object is required. So, to answer your question, this scenario should make you stop and think, but doesn't necessarily mean you're doing something wrong.

Answer 3

My take on this is that if you need a resilient clone of a database object then your best bet is to perform a deep copy - at least copying all those items that may change at some point in the future. I don't see that there's much alternative other than implementing some copy-on-write style of 'clone' which whilst it might be right in some circumstances would introduce a whole plethora of additional logic & complexity.

Answer 4

It depends on what your relational tables represent, and which of your entities are grouped together to build a "business object". For example, if you have a relational model of a content management system with a table "Postings" and a table "Text_lines" where every Posting consists of a list of text lines, a valid copy of your business object "Posting" will most likely be a deep copy of a "Postings" entity together with the belonging Text_lines entities.

On the other hand, having two tables "Department" and "Employees", a correct copy of a department most likely is not a deep copy, (at least, if each employee is associated with exactly one department at one point in time). In a relational database scheme, those kind of differences will often go hand-in-hand with referential integrity checks assigned to the relations. The relation "Postings"->"Text_lines" can be modeled using a foreign key constraint with an "ON DELETE CASCADE" integrity (of course, if your database supports that). The "Department"-> "Employees", however, should not have such an "ON DELETE CASCADE" check on it.