Should I use a EAV model or not in laravel?

Question

I am building an 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 a DB.

What would be the best approach?

I need to be able to filter on these custom fields--like an advanced filter--and performance is key too.

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

Answer 1

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 = Category::whereHas('CategoryAttributes', function ($query) { $query->where('key', '=', 'color'); $query->where('value','=', 'blue'); })->get();

Answer 2

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.