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?