Laravel DB Custom Fields with EAV-Model: Worth It?

This is a free lesson from my upcoming course on DB structure in Laravel. Let's test the flexibility and performance of a so-called "EAV model" of structuring the DB.

- - - - -
Support the channel by checking out our products:

[metadjace]

Every single videos of yours are worth watching.
Even if you already know the concepts you will surely learn something new.

I just learnt that where accepts array.

Can't believe i didnt know that.

[christianosueke]

I think you make absolute sense.. One of the disadvantages of DB normanilization is that you sacrifice speed and performance for flexibility.

[zachariascreutznacher]

This was very very informative, thanks for your content. And it seems you were totally right, the maintainers now abandoned the package due to performance issues.

[poplach]

If you have an internet shop of many different products with really different attributes, it's worth creating an attributes table per category.
Best solution that solves ALL your problems - easy to add/edit attributes and super fast search by attributes.

Here are basic tables:

TABLE 'categories':
-id
-title

TABLE 'attributes'
-id
-category_id (foreign key)
-title (example: 'size', 'manufacturer', 'origin')
-field_num (integer)

TABLE 'attribute_values'
-id
-attribute_id (foreign key)
-title (example: if attribute is 'size', this value may be 'XL', 'M', etc)

TABLE 'products'
-id
-category_id
-title
-attribute_1 (int, index)
-attribute_2 (int, index)
-attribute_n (you may put as many as you need per product. I'd say you never use more than 50)

Here's the thing - why 'attribute_1' is integer? Because it stores the ID of 'attribute_value'!

So how you'd use it?
Well, consider that search:
1. you query attributes per category (fast) - all you need is to know which field_num to query with what values. Let's say for this category 'field_num 1' is 'size' and 'field_num 2' is 'origin'.
2. you query all attributes values that you need (all possible sizes and origins)
3. you query products with 'field_num' and its 'attribute_value id' like this: 'select * from products where attribute_1=17 and attribute_2=25'
In the query '17' refers to ID of 'size' attribute value 'XL'; and '25' refers to ID of 'origin' attribute value 'China'

In the end you can have billions of different attributes and billions of their values.
P.S. if you have dynamic value, like 'serial number' or something that is ALWAYS different for each product - it's easy! you just store it in 'attribute_text_1' field right in your 'products' table!

[adityakadam]

In relational problems like this, it is better to have a separate table for attributes and have it linked to each product id in the products table.
Thanks for the video. Great explanation.

[matrixlander]

Whenever I use EAV tables, usually for settings, the attribute types are kept as a separate table and foreign keyed to the attribute values table. That way, the attribute_type_id column can be indexed and queries are faster.

So when querying by an attribute, I would query using an AttributeValue model, where attribute_type_id = 'some_id' and the use the belongsTo relation to load the parent (e.g. Product)

Performance is usually good and this scales well.

[beatnu]

Another great video! Thank you! EAV reminds me of Magento and i don't like that :) Thank you for the clear explanation.

[RichardTippin]

Awesome information! I usually opt for more columns within a single table myself.

[AdiHidayat]

it's so cool, thanks for the tutorial sir!

[taamcyat]

I've used the EAV model with success to create my own on the fly dynamic form/questionnaire builder(like my very own SurveyMonkey). The thing is, I use MSSQL: Used the datatype VARIANT so that i only have 1 value table(performance advantage 1). The variant type in mssql holds anything! Like a var in programming. I indexed all the tables. I also use the dynamic sql and PIVOT features to generate the flat table - a table used for reporting that is a pivoted version of the values table. Reports are generated from the flat table. The pivot operation is insanely fast! I also use strongly typed, highly optimized and tested stored procedure to run my custom sql to gen the flat table(performance advantage 2). I use a dynamic sql to inject(legally using parameters) a list of attributes required for the reports. It's a big performance advantage to create your own tested and optimized TSQL(in a parameterized stored procedure) over allowing the middle-tier ORM to loosely generate the query for you. I seed the model with millions of records and the performance is amazingly fast! I've had nothing but success with it. It works! Magento uses a model like this. Maybe Survey Monkey uses something like this too. Not sure. I really needed the custom dynamic type to EAV model to quickly(dynamically/on fly) build forms to implement in our health information system to rapidly implement forms in each department in our health ministry. There are like a million departments in any health system. Horizontal column propagation would be a nightmare to implement and maintain as you can imagine. My point is, from an employee programmer's perspective is that it can be successful if you bare-knuckle it and build it manually and write your own optimized vetted queries and use that dynamic(sql) pivot and the Variant data type so as to only have 1 value table. The EAV model offers high adaptability at minimal effort. Look at how adaptable and effortless Survey Monkey is(although they may have not necessarily actually use an EAV model - not sure). EAV is for the lazy ;) it saved me thousands of programming hours.

[Наиляитворчество]

I use EAV model, but for filtering records, i use elasticsearch. It’s rly complex but so fast

[SXsoft]

i remember whereHas making a subquery in your main query, then you have the separate query for eager loading attributes

[NathanBudd]

Would an alternative be to store the additional attributes as JSON in the products table?

[stestrellajohnrobina.]

Hello, Can I ask about how can I manage when the Product Variant has different prices, quantity and stock status. For example for the Sizes Small = 350 for Medium = 440 and the stock status and quantity of this may change. and Hows the product that has no Variant??

[atatopatato]

the main advantage of using custom fields is that the end user can create his own attributes dynamically

[drevendyharianto]

Cool, thank you for the explaination.

[JimOHalloran]

You can tell how long someone has worked with Magento by asking "So, tell me about EAV?" The more swearing you get in response, the more experienced the Magento developer :) I think the Magento EAV implementation has some particular issues, but EAV is a tool I'd think about long and hard before I committed to it. Which isn't to say it should never be used, but I'd want to be careful.

[nicafloki]

Thanks for the video. What is the name of the plugin on browser that show Lavarel queries executed?. Thanks in advance.

[tal.gerafi]

Hi! What if I need nested custom fields and / or custom field type array? how would you save these?

[equalsStaR]

You don't actually use whereHas in Laravel because it uses "exists" which is extremely slow. I think the best option is to just inner join second table with a where clause... it does the job and is super quick.

ps. i think there was a package with whereHas as join instead of exists