CODEWITHSUNDEEP
mysqldoctrine-ormdatabase-schemaentity-attribute-value
jonathancardoso
jonathancardoso

Reputation: 12707

Table with custom fields

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:

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

Upvotes: 0

Views: 1082

Answers (3)

rodrigovr
rodrigovr

Reputation: 454

The kind of structure that you are trying to create using the EAV can be modeled in many different ways.

My first choice would be to create a single table for each data type that you need to store, say for example 'integer', 'text' and 'datetime'.

The schema, for the 'customfields_text' table would be:

object_id, field_name, text_value

Primary Key = object_id + field_name
Index at text_value

The same schema will also apply to 'customfields_integer' and 'customfields_datetime'.

When querying, you will be able to:

SELECT * FROM objects o JOIN customfields_integer ci ON o.id = ci.object_id WHERE ci.field_name = 'my_custom_attribute'

Upvotes: 0

Uours
Uours

Reputation: 2492

( 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 .

Upvotes: 1

karthik manchala
karthik manchala

Reputation: 13640

If you are using RDBMS and dont want to switch, the only option you have for handling such usecase is by using @JoinColumn with @OneToMany mapping for persisting the entity for custom fields instead of storing directly.

If you are using any ORM reads and writes are handled efficiently and querying is also easy.

Upvotes: 0

Related Questions