RM 1.1 Difference between xf_resource_field_value and xf_resource.custom_resource_fields?

[DeltaHF]

I'm creating an add-on for the Resource Manager, and found that the xf_resource_field_value table and the custom_resource_fields column in the xf_resource table appear to contain the same data in slightly different formats.

Just curious: why is this data duplicated?

 

[Chris D]

The column in xf_resource is a cache. It enables the information to be accessed without additional queries.

 

[Mike]

In most cases, if you see one table with a "structured" version of data and another where it's serialized/JSON, the serialized data is simply a cache -- the structured version is considered the canonical.

 

[DeltaHF]

Cool to know; thanks.

Is there a reason xf_resource_field_value stores custom fields in discreet rows instead of columns?

 

[Chris D]

How could it store them any other way?

What additional columns would be needed?

Any additional data that needs to be fetched can be done using joins.

 

[DeltaHF]

I expected each field value for each to be stored in its own column per resource_id:

+-------------+-------------------+-------------------+----
+ resource_id | custom_field_id_1 | custom_field_id_2 | ...
+ 1           | "foo"             | "bar"             | ...
+-------------+-------------------+-------------------+----

Instead, each field per resource_id is stored in its own row:

+-------------+------------------+-------------+----
+ resource_id + field_id         + field_value + ...
+ 1           + "custom_field_1" + "foo"       + ...
+ 1           + "custom_field_2" + "bar"       + ...
+-------------+------------------+-------------+----

I suppose this is because different resources could, depending upon their parent category, contain entirely different custom fields?