what is the best practice for updating a large bo

dstarkey posted on Tuesday, September 19, 2006

I have a large employee object with approximately 70 properties and corresponding database fields.  This employee object also contains a few bo child classes as well.  My question is it better to build the update sql string within the bo or call a sproc that requires each of these properties as input parms?  My idea to build the update would retrieve an original bo from the database and compare it to the bo in memory.  Another constraint that may force me to implement in this fashion is the fact that some fields are designated as key fields and can only be update on approval.  So I already have to check if any key fields have been changed, so it would not be too difficult to build this update sql string on the fly.  Any input regarding this is greatly appreciated.  For example: An address change would only send/update address fields in the database.

RRorije replied on Wednesday, September 20, 2006

My (maybe naive) idea would be to keep track of the data retrieved from the database in the dataportal_fetch, by using a variable for each column in the database. When you create the update string you only need to update the values that are changed, i.e. different than the values retrieved from the database. Probably this is also possible using stored procedures, but I do not know how.

However, I could imagine that any senior programmer, which I am not ;), could come up with a better idea.

JHurrell replied on Wednesday, September 20, 2006

I have a few questions.

Do you really need to update all of those 70 fields within a single transaction? If so, would it be possible to break the 70 fields into a smaller set of objects that would then be children of a larger employee object?

Employee (EditableRoot)
   EmployeeContactInformation (EditableChild)
   EmployeeResume (EditableChild)
   EmployeeCompensation (EditableChild)
   EmployeeSupervisor (EditableChild)

If they don't need to be part of a larger transaction, could you break the properties into a set of smaller objects?

Employee (EditableRoot)
EmployeeContactInformation (EditableRoot)
EmployeeResume (EditableRoot)
EmployeeCompensation (EditableRoot)
EmployeeSupervisor (EditableRoot)

Even though your database may be flat and all the properties exist in a single table, you can still update only the fields you need.

- John