Table with custom fields

Question

We are currently working in a CRM, and one of our tables need to have multiples field which should store custom data sent over an API, the client may need many of these fields.

Initially, we had something like the following:

Entity
id, -specific fields to the entity-, customField1, customField2, customField3

But then we were told that only 3 fields are not enough, and it was requested at least 100 fields.

So with that in mind, I hit a dilemma, should I use a EAV like structure, or just add the fields to the table (customField1, ..., customField100)?

The EAV like structure was something like:

Entity
id

EntityCustomField
id, entityId, fieldValue

The main issue here is querying, which becomes more complex (and slow?).

Some more info:

• Only strings are going to be stored in such fields.
• The fields need to be indexable and possible to search individually each one. (So encoding the data as JSON is probably not going to help here).
• The usage of non-relational databases are not possible currently.
• This table is going to have more writes than reads.
• We cannot switch RDMS.

Anyone had similar problem, and found a solution, or have experience with any of the possible solutions proposed?

Answer 1

( I am not acquaint with doctrine2 )

Just an idea :

Why not have a table to hold field values :

CREATE TABLE field_values_table (
    id int(11) NOT NULL AUTO_INCREMENT
    ,field_value_1 varchar(100) NOT NULL DEFAULT ''
    ,field_value_2 varchar(100) NOT NULL DEFAULT ''
    ,field_value_3 varchar(100) NOT NULL DEFAULT ''
    ,field_value_n varchar(100) NOT NULL DEFAULT ''
    ,PRIMARY KEY (id)
);

And another table to hold corresponding field names :

CREATE TABLE field_names_table
(
    id int(11) NOT NULL AUTO_INCREMENT
    ,field_name_1 varchar(100) NOT NULL DEFAULT ''
    ,field_name_2 varchar(100) NOT NULL DEFAULT ''
    ,field_name_3 varchar(100) NOT NULL DEFAULT ''
    ,field_name_n varchar(100) NOT NULL DEFAULT ''
    ,PRIMARY KEY (id)
);

And either

• consider one-to-one relation between above two tables with id as common field
• or have another table as a relation table between the above two tables

Say :

CREATE TABLE field_name_value_relation (
    id int(11) NOT NULL AUTO_INCREMENT
    ,field_names_table_row_id int(11) NOT NULL DEFAULT '0'
    ,field_values_table_row_id int(11) NOT NULL DEFAULT '0'
    ,PRIMARY KEY (id)
);

So if you were to search a field named country having value as New Zealand ( you might not achieve it completely by an SQL query alone but ) a very high level SQL query could be :

SELECT
    *
FROM
    field_names_table
    LEFT OUTER JOIN field_name_value_relation
        ON field_name_value_relation.field_names_table_row_id = field_names_table.id
    LEFT OUTER JOIN field_values_table
        ON field_values_table.id = field_name_value_relation.field_values_table_row_id
WHERE
        field_name_n = 'country'
    AND field_value_n = 'New Zealand'

It is not that simple as you would not know which all columns the country field name might be in the field_names_table .

Anyway I hope this gives some thoughts for you to accomplish your requirement .