I need to build an application where there are generic entities (let's say articles, pages, nodes) where the user can add custom fields.
I've seen the approach that the most popular php CMS (wp, drupal) use to get this goal; They all have the base table with the minimum fields (e.g. title and body), and then delegates all the other fields to other tables, for example:
table node
id | title | body
table field_foo
node_id | field_type | field_value
table field_bar
node_id | field_type | field_value
// and so on
This, in a full mvc environment is pretty logic; The field controller handle every field separately.
But talking about performance, loading a single node will require many queryes - or many joins.
I've taken a different approach (even becose my app does not provides any base field): for every field i add a new column on the base table that will store a raw value, then a table for every field that need it (multiple fields for example, or reference to other entities) and an relation table with just indexes entity_id | field_id (that table actually do other kind of jobs, as keep track of versioningn and kind of relations between entities)
So with a single query i get all the raw data from an entity, then the field controller knows (when required) how and where to load the real values of that fields.
The column's type in the data table (table_entity_data) is the best guess for the field data: for text is text, for decimal is decimal; only for multiple fields (that have theyre value outside that table) is array (and the real data_type is in the _field_foo_value.entity_value_ column)
Assuming that the entity structure wont change often, i tryed to normalize the structure..
Given that other big projects handle this in a very different way I have come to doubt about my implementation, and wondering what kind of problem will happen with my hibryd structure:
table entity
id
table entity_data
entity_id | field_foo_rav_value | field_bar_raw_value
table relations
entity_id | entity_field_id | field_id_value
table field_foo_value
field_value_id | entity_value
// lets say field_bar is a single text field, there no will be another table:
// entity_data.field_bar_raw_value contains the real value
Any suggestion?
p.s: i know this question is kindly generic, feel free to flag for closing if not appropriate.
