Category Model 1.5

[jbee]

This is a "work in progress" design document to note down thoughts on how to change the category model so it becomes manageable again.

Observed Problems

• the current model does not scale
• uniqueness of COCs cannot be enforced without degrading performance to an unreasonable state
• deletion of CCs with 3 or 4 digits number of COCs is practically not possible. Even when no data exist this is a very slow operation. With data it will crash the server before it finishes

Identified Causes

The main issue with the current model is that is is too flexible and allows reuse and duplication in places it should not.
This makes it necessary to run expensive queries to see if certain changes can be permitted.
Those queries don't scale confining the usage to small numbers of C in a CC, COs in a C and total COC in a CC.
The model needs to be changed so most of the queries become unnecessary because the situation is known by design.
That means uniqueness is enforced by design and removal can be done with a single update ... where and/or delete ... where clause without checking further implication because the scope by design can only be the one intended by the deletion operation.

Design issues

• COCs should be unique but uniqueness cannot be enforced when the relation is modeled as a : relation (mapping table)
• CC should be a unique combination of Cs but uniqueness cannot be enforced when the relation is modeled as a : relation (mapping table)
• COs for/in a C should be exclusive but are not (COs should strictly belong to a C and never exist in isolation)
• COCs should be exclusive to a CC but are not (COCs should be strictly belong to a CC and exist in isolation)
• a COC ATM can have 0,1 or many CC references in DB but in Java model it is only 1 CC

Related Design Issues

• Metadata that is a key/dimension of aggregate data (DE, OU, P, COC) needs special treatment for imports (create/update/delete). The current importer is not suitable to process these as they require a manually organized and optimized bulk processing that does a "full picture" before/after analysis that is not build around single object consideration and handling.

Suggestions

• Make COs a JSON array in a C (they need to preserve UIDs)
• Make COCs a JSON array in CC (they need to preserve UIDs and long IDs, data value cannot use FKs any more but simply a number which also allows to delete CC+COCs but keep the data rows and potentially clean it up later with a job)
• Consider to have data values not reference COCs but CC + CO

Cleanup Chores

• C, CC, CO and COCs should only be created using the REST API or the importer. ATM there are many service methods that are only used by tests to setup these objects. This makes tests and prod use entirely different code to manage these objects. Such tests should be refactored and the service methods be removed.

Transition

1. Make COC have a FK column to 1 CC (remove/resolve categorycombos_optioncombos)
2. drop column categoryoptioncombo.attributeValues (cost/benefit is super off)
3. drop column categoryoptioncombo.translations (cost/benefit is super off)