RDBM dynamic table structure

Question

I am trying to create a dynamic table.

ID  |TABLE_NAME    | COLUMN_NAME| VALUE | CRITERIA_COLUMN | CRITERIA_VALUE
------------------------------------------------------------------------------
1   |PRODUCT_LIST  | HANDLE_FEE |  5    |  COUNTRY        |  AUSTRALIA

This shows which column needs to be updated. This data translates to

UPDATE PRODUCT_LIST
SET HANDLE_FEE = 5
WHERE COUNTRY = 'AUSTRALIA'

But if I want to add more criteria like

UPDATE PRODUCT_LIST
SET HANDLE_FEE = 5
WHERE COUNTRY = 'AUSTRALIA'
AND STATE = 'WA'

How would you change the dynamic table to achieve this update statement?

Answer 1

In relational theory and in SQL dbms in general, this "dynamic" table has a special name. It's called "table".

Using a table like this won't "record all update history". It will only record parts of update statements for whoever is using your front-end application.

Assuming that, by update history, you mean all the insert, update, and delete statements, that can only be done by auditing or logging.

For example, you can configure PostgreSQL to log all insert, update, and delete statements (and a few others) by setting log_statement = mod in the file postgresql.conf. You can configure SQL Server for change data capture or change tracking. SQL Server also supports C2 audit mode (deprecated) and common criteria compliance.

If you must go down the path you seem to have set for your employer--and that's a really, really bad idea--you would move the pieces of the WHERE clause to another table, set a foreign key reference "your_table_name", and assume that all related rows in the WHERE clause should be ANDed together. But then later accommodate the need to use OR sometimes. And again later accommodate arbitrary numbers and levels of nested parens. And again later accommodate the fact that you need to support more operators than just =. And again later . . .