Monday, August 6, 2007

Adding new fields... to an existing record or a new one?

At the very start of every PeopleSoft project we're confronted to the task of defining the Development Standards. One of the recurring discussion points is what to do when we need a new field in a record.

a) Create a sibling record (same primary key), adding the new field as an attribute to that record.

b) Add the new field in the original record.

At first, the a) option seems the cleanest one as it does not modify standard records and it sounds like it will easier to maintain through upgrades and fixes.

However, this option also has its limitations:
  • Querying the table now would involve joining two tables. This leads to additional complexity and performance issues (particularly if customizations have been made by adding sibling records to multiple records in a query).
  • When creating a query, you may need to filter on both the standard record and the sibling one. In that case, you won't be able to use an index on both filters, as the fields are placed in different tables.
  • Adding a sibling records means you need to use an additional scroll area in components showing both the original record and the standard one. In some situations, this may put in a situation where the 4 allowed scroll levels (0, and 1 to 3) are not enough.
Mainly due to performance reasons, my preferred option is b). In order to keep a clearer track for upgrades and fixes, what we normally do is to place new fields in a subrecord defined explicitly for customized fields. In such way, it is always easy to identify the customized fields in a record.

What is your experience on record customization? Which of the two options you prefer?

8 comments:

Luana said...

Well said.

Jim Marion said...

@Javier, I can certainly understand your perspective. I agree that the sibling record pattern adds to the complexity of the data structures. I think it is important to point out, however, that modifying delivered records may have an impact on Cobol stored statements, SQR's, and AppEngine SQL. Consider the case where a modified record is used in "INSERT INTO" SQL statements in Cobol, SQR's, or AppEngine SQL statements. Adding a field to this record will also require updating Cobol stored statements, SQR's, or AppEngine SQL statements.

When using option b, it is critical that the developer conduct a thorough analysis of the record definition's usage. Option a does not require this level of analysis. Option a is safer from this perspective.

Either approach is acceptable. A developer (or architect) must way the benefits and costs of each alternative.

Besides the two alternatives you present, I also advocate a modified version of the sibling pattern. Rather than use another scroll (to avoid having 2 data records at the same level), I actually use a Derived/Work record and PeopleCode to place data at the same level as the delivered record. I outline this technique in a book I'm writing that will hit the market sometime in 2010 (I'll post details on my blog). Of course, this adds complexity to the design and maintenance of the component from a developer's perspective, but from a user's perspective, it may produce a result that more closely matches the user's design.

I'm just writing up some documentation on the "sibling" pattern and thought it might be helpful to add some justification for using the "sibling" patterns.

There are a lot of customers that are modification shy for fear of upgrade rework. I would rather see a happy and productive user than a fearful developer. Failure to customize applications generally leads to dissatisfaction among users and developers alike.

Thank you for sharing your thoughts and pushing for customizations that make users more productive.

Fred said...

I usually go with the custom sibling route mostly to avoid the possibility of SQL errors from objects within and outside of Peoplesoft that reference the delivered table.

In reading Jim's response regarding the modified version of the sibling pattern.... I was wondering if it took into account Correction mode.

In other words, when using a derived field and peoplecode to sync values with the custom sibiling table that contains EFFDT, do you have a clean way make use of the delivered js correction functionality that stops a user from changing historic data?

I went this route before and added peoplecode functionality to mimmick the correction error if the user tried to change historic data within that derived field but it seemed like it could be done by tricking the system.

Thoughts?

Jim Marion said...

@Fred, good point about correction mode. If you are modifying a component, then you will likely know the business logic around that component and correction mode, etc. Then you can use the PeopleCode system variable %Mode to determine if you are in correct history mode, and then respond accordingly.

Actually, now that I think about it Fred, yes, the "modified sibling" approach would consider correct history because I don't actually write data to the database until SavePostChange. As you know SavePostChange doesn't happen until all edits are complete. Therefore, it would not be possible to execute SavePostChange from a component if a user tries to edit a history row in UpdateDisplay mode.

Javier Delgado said...

Thanks Jim & Fred for your feedback. I found Jim derived/work approach quite interesting, as it's a design pattern I haven't explored before.

Anonymous said...

@Jim, I like your approach but I'm wondering about what this does to Record level auditing. If you wanted to Audit these new custom fields would you have to manually do so in the PostSave peoplecode? Or is there a way for the normal Tools auditing to work?

Anonymous said...

When using the derived/work record approach, what is the best way to handle row deletes? The RowDelete event doesn't offer you the ability to SQL delete the data from the physical table. Just curious if there are any suggestions for this...

Anonymous said...

In the PostSaveChange for the row I would do something like this:

If GetRow().IsDeleted Then
/* Do Your Delete Stuff */
&sibRecord.Delete();
End-if;