0

I am currently working on a medium-sized web app project that is already in production using Laravel framework and MySQL 5.6.40. My clients have been asking for the ability to add additional attributes to the orders table to keep track of some additional dates.

At first to avoid bloating up the orders table, i was planning to create a new table called order_custom_fields that will link to the orders table and have a column for each of the custom field. But this will make it not conventional if more custom fields need to be added in the future for different clients or if the custom fields will be attached to different tables.

I have since stumbled upon the EAV model and thought that it seems like it would solve my problem if not for most sources saying that it should be avoided mainly for performance reason and possible difficulty when querying.

Since mysql 5.6 still does not support json, is the EAV model likely my most practical choice as to avoid bloating up the orders table with custom user field?

Does anyone have a similar case and can provide some insight?

More information:

  • The orders table currently already has 9 foreign key, 20 attribute fields, programatically linked to many other tables, and already has close to 50,000 records.
  • The Laravel framework handles all my database queries, and can programatically create polymorphic relation between database tables so only 1 EAV table will be required in my case. Also, the data in the EAV table can simply be a varchar/text and be casted as required in the program.

In laravel style, the table will be something like:

+-------------+-----------+-------------+------------+
| entity_type | entity_id |  attribute  |   value    |
+-------------+-----------+-------------+------------+
| Order       |         1 | custom_date | 2020-02-02 |
| Order       |         2 | custom_date | 1991-01-01 |
| Product     |         1 | custom_name | phone1     |
+-------------+-----------+-------------+------------+
  • eav results in some horrible queries at time. If its a bounded set of extensions I'd just add those columns. 5.6.10, wow - been almost 7 years of stable fixes since that was released. – danblack Feb 19 at 2:38
  • why not just store the JSON as text? – Jonathan Fite Feb 19 at 2:38
  • @danblack i don't really want to add those columns purely because only 1 specific client will be utilising it.. at least for now anyways.. Also i made a mistake on the version. it's actually 5.6.40.. – Helioarch Feb 19 at 3:14
  • @JonathanFite if you meant having json to replace the attribute and value column in the EAV table then i agree that i can do it that way but i thought maybe this way i can still query a certain attribute to try and reduce the query result – Helioarch Feb 19 at 3:17
  • Ok, I'm with @JonathanFite a TEXT column with the JSON parsing in the application. – danblack Feb 19 at 3:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.