Laravel Custom Fields: JSON, EAV, or Add Columns Manually?

good video. Can you show me the sale structure of EAV model the table of sale detail. Wether it contain only the product id or the product and attributes as well
need prompt reply

I’m looking into the multiplex package to add timable meta to a model. Looks like an interesting alternative to Json, eav, or multiple columns

Why use a separate table for the property names? You could also add the property name as an enum and add new values if needed.

I have been thinking about the DB structure for years. Should similar elements be crashed into separate tables or all in one. In fact, one could be given to one, but there would be many empty columns (null). Currently, it would be several hundred thousand records. So it is appropriate to save space ... There is probably one way out. Connect the above methods to one using "normalization". The common part like "ID, name .." + various types, tags, etc. as an ID in a separate table, and a specific part for a given record in JSON (paintings, links), although they could also be in separate tables.

Thank you very much for making this video. I was struggling a lot on DBA or DB Structure when I first started coding. I used to use the "extra column" method until I get laugh at at work. I picked up the other 2 methods as I progress but didn't really understand them. Especially when to use them or why to use them. Your philosophy has ended my conern on over engineering projects. Thank you

Do you think it's ok to add one more field in the DB for convenience? Example: I have "Company" that hasMany "Projects" that hasMany "Instances" and in the Instances table I added the organization_id so I don't need to load projects when I need to know what Instances the company have. Data rarely updates and I change the organization_id and project_id on the instance when necessary. Everything feature tested of course. I think it's simpler this way, although it's redundant, I'm not sure it's good practice.

We have used all the approaches in my company.
Mostly we are using EAV to make everything more parametrized or dynamic.
Json can be very complex but in some situations is a good approach.
I am not a big fan of adding extra columns... But there are some projects or cases like reporting that this is the only way for better performance or simplicity.

Awesome bro, thanks ❤

WhereHas performance is bad.
It could be a good idea to do the first Query using joins.

That's an insightful video. Thanks for sharing it. For our Headless CMS app we faced a similar problem like this. The problem was to store meta info about an item as a key value pair considering that value could be of type anything and key is literally anything. The best solution was to create a separate table with 1 to many relationship and overall the performance
was okay. We started with JSON column but it isn't really scalable as you have to do good amount of reread and rewrites.

Hi @Laravel Daily! Did you already tried to work with MySQL XDevAPI? Im thinking about to try to use it in my Laravel 9 and MySQL 8 project... That way you can get the best from SQL and NoSQL world... Could you please create a content for Laravel 9 and MySQL XDevAPI? I guess it is promissing and underused...

In my early days, I would add more columns but recently, I've shifted to using JSON columns.

I tend to have a `meta` or `options` json column on important models and I can put properties there like "defaultOrder", "position", ect... for "value object" type data.

Sometimes I have put more important data in there, like a "dashboard": [{title: 'My View', widgets: [], colour: 'blue'}], but it always becomes hard to work with via eloquent, so I eventually move it to seperate models. And the seperate models isnt that hard really.

Another problem with JSON was I couldnt index columns (Without virtual columns) and the size of the table grows expodentially, so if these are concerns, just stick with seperate models.

Maybe when loading the index page of the products, load only the products without properties and load properties only in single-item pages.
Results by laravel benchmark with 10,000 products. Note: all products have properties .
"Product::with('properties ')->get()" => "1,997.747ms"
"Product::all(['size','color', 'height'])" => "89.559ms"
"$product->load('properties ')->get()" => "126.606ms"

I ddnt even know u can query json column like that

I created a small ecommerce project to learn about Stripe/Paypal integration a couple of months ago and i went with an "Attribute" table with Type (Enum, Color/Size), Name (Red, L), and a Value (Hex of the color, L) 😅

For better JSON column performance, I found that we can add a virtual column that mirrors the JSON path and build an index on the virtual column. I hope to see an example for this

I'm working with a legacy system written around 15 years ago, some of these tables have over 200 columns with only around 50 actually being used. With over 250,000 rows I might have 10 or 20 with the fasciaMountingHardwareColor column actually containing data, there's no way to set that column in the application so whatever was populating it was removed at some point. Just pulling the rows you want and finding the columns you're looking for is a nightmare made worse because some column names are camel case, some are snake case, some don't reflect what they hold and some are misspelled (dateLastModifed) but for some reason instead of renaming it they just used the misspelling throughout the app code. In one table the modelID column actually contains the the signtypeID pointing to the related signtype table, in another table that column is just named model, in another table it's named signID. Incredibly this company has been running on this mess all of these years.

Where is the link to the article?

I've used the EAV pattern in a couple of past projects and while initially great, it will become very cumbersome once the number or records as well as requirements for reporting and statistics grow over time. Reporting queries become more and more complex, and performance can degrade quite a bit when combining those complex queries with large datasets. Then you'll start implementing workarounds like materialized views (which MySQL does not support natively, so you either have to update them via an externally-triggered query or ON INSERT/UPDATE/DELETE triggers on each of the involved tables) which are optimized for easy querying and performance.

With native JSON-Support, you can now have the best of both worlds: Store the flexible part of the model as JSON, and if it turns out you need the data in a query, you can add a virtual column to the table which will extract its data from the JSON. Once performance becomes an issue you can also define an index on the virtual column.

For really complex reports, a materialized view or even transferring the data to a separate datastore may still come in handy here and there, though - but with any luck, this will now be postponed to a much later point in time ;-)

For scenarios where we have fewer than 3-4 properties and use them regularly, I will add them manually, while for others, I will use JSON fields.
Databases like MySQL and PostgreSQL are now powerful for working with JSON fields. We can index them, or we can even use generated columns.
For more complex scenarios, I consider using NoSQL databases like MongoDB,...

In the EAV model wouldn't be better to query the property id instead of the value? the property id is indexed so the performance would be better

I mostly prefer the columns and JSON way which is more easy and convenient.

Is it possible to make relations through json columns?

as you said it depends on what you are going do.. hard to decide! thanks for this video :)

Good Example. Thank you
I have one doubt in the EAV i think we can write to where conditions no need to go for two time whereHas().

How about your opinion?