Title: Should I use a EAV model or not in laravel?
Tags: php;mysql;laravel;database-design;entity-attribute-value
Question: I am building a app and I have in my app around 20 categories and each category has some custom fields. So a user picks a category and fills in the custom fields. After that I need to store the values in DB. Can you suggest what would be the best approach to do it. Also I need to be able to filter on this custom fields - like a advanced filter and a performance is a key too.

Should I just make for each category a separate table with their custom fields in such case?

Thank you for help

Dany

Comment: thank you @JohnJoseph for the answer and where would you store the selected values for the custom fields in 3 table custom_fields_values?

Comment: I'll only roughly answer the last question as the rest are too broad and non specific. category should be a table and category_custom_fields should be another, with the custom fields table storing a category ID to link them together. Ask questions for the other issues as and when you come to them once your database model is designed.

Here is the accepted answer: There are two options that I can think of.

1) Create a JSON field called something like attributes in your category table. And then store basically an array of Key Values in it.  That will present some challenges when querying on attributes though. I know there are ways around it but i've never needed it so I do not know.

2) Create a Category Attributes table in your DB that goes something like this

```cat_id - int
key - varchar
value - varchar
Composite Index Unique on [cat_id, key,value ]
```

Then create a Category Attribute model in laravel and define a hasmany relationship where Category has many Category Attributes

then querying your categories would go something like this

```$categories = Category181a:6c1c:918f:f3a4:2e8c:61d6:1a08:6b16whereHas('CategoryAttributes', function ($query) {
    $query-&gt;where('key', '=', 'color');
    $query-&gt;where('value','=', 'blue');
})-&gt;get();```


Here is another answer: The benefits and drawbacks of implementing an Entity-Attribute-Value (EAV) model in a relational database are the same with Laravel as they are with almost every other framework or language. I don't see that using Laravel really has anything to do with the question.

If your use case needs the flexibility of EAV, and you are prepared for the additional complexity (by an order of magnitude) and prepared for a big performance hit... if those aren't breakers, then use the EAV model.

But if you don't require any of the benefits of EAV, then by all means, avoid the drawbacks.


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 2, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2016:00:12/21/16', 'FavoriteCount': 1.0, 'PostTypeId': 1, 'Tags': '<php><mysql><laravel><database-design><entity-attribute-value>', 'Title': 'Should I use a EAV model or not in laravel?', 'ViewCount': 4268, 'id': 41265390, 'user_AboutMe': None, 'user_AccountId': 2089782.0, 'user_DisplayName': 'user1859876', 'user_Reputation': 37.0, 'user_Views': 9.0, 'user_id': 1859876.0}}