A reader, September 24, 2025 - 10:02 am UTC
Things have been anonymised for the purpose of the question
September 25, 2025 - 6:27 am UTC
phew :-)
Fears and suggestions
Stew Ashton, September 24, 2025 - 10:05 am UTC
Once I was tasked with "approving" a similar design. I said, "I don't want to OK this, but I will if you promise you will always search on master key and attribute!" They were quick to promise, and almost as quick to start searching on attribute and value. Wretched performance!
If you do this, I suggest:
1) Index-organized table
2) primary key only on master key and attribute, unless they really want multiple values for the same attribute.
As an alternative suggested by the Oracle JSON team, put a JSON object in the master table as a "flex field". All the attributes are there in the master row itself, and an attribute can have an array of values. As searches on attributes appear, JSON indexes can be created to optimize the queries.
Revised thoughts
Stew Ashton, September 25, 2025 - 8:21 am UTC
I didn't see the "things have been anonymized" comment until I submitted my own comment 3 minutes later. I'll start over.
1) Primary keys are supposed to be immutable. A row consisting entirely of primary key components should not normally be updated. Has the developer promised that? Your proposed solution would be more appropriate if updates are expected.
2) The "intersection table" Connor mentioned has primary key components that are foreign keys to other tables. The foreign keys reference immutable primary keys in those tables. How many of the requested primary key components are foreign keys?
3) The request would allow multiple rows with the same values in any two of the three columns. Is that really what the developer wants?
4) An index-organized table would be worth considering.
5) As with any index on more than one column, search patterns should be considered when deciding on the order of the columns.
September 29, 2025 - 2:46 pm UTC
Good questions Stew.