by pricerc » Fri May 07, 2021 2:15 pm
Jiwa is one of two ERP packages that consume my working life.
The other product has been around since the 70's and was originally written in COBOL on some UNIX flavour. So it has some baggage. And until the latest major release, that included support for data in ISAM instead of SQL.
They used to store all their custom data in one big data table: From memory, the columns were something like: FormCode char(6), FieldCode char(6), KeyValue char(100), DateValue datetime, NumericValue decimal(18,6), AlphaValue char(255).
There were all sorts of things wrong with that, especially performance, and we used to do anything we could to discourage people from using them.
But a few years ago, with the introduction of their previous major release, they came up with a new style of custom data, where each business object that accepts custom forms can create a custom SQL table, with actual SQL columns for each custom field. We no longer hesitate to put in custom data on these systems. They decided to us a special character to identify the custom form fields, which was a bit sucky (always have to quote the table name in SQL), but they work well.
That release had a system-wide setting for which style of custom form you were using: 'old' or 'new', with a one-way 'migration' tool.
Jiwa's current system is already pretty good, in that the custom field schema is well defined, and available for most business objects. And well encapsulated in a generic 'custom data' library.
But y'all would gain a fair bit of performance if you could figure out how to change those schemas into columns in a table instead of having to 'unpivot' the data for consumption outside of Jiwa.
I understand that having plugin-specific custom data (which is cool) would complicate this, but I wouldn't have thought too badly - you could assign an abbreviation to each plugin, and prefix column names with that.