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

Let's compare three different DB structures for flexible DB columns, needed for e-shops and similar projects.

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

- - - - -
Other places to follow:

[javierrenteria]

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

[EngBassemNagy]

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

[HadToChangeMyName_YoutubeSucks]

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.

[MariosVasiliou]

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.

[natenatters]

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.

[pekorabestgirl]

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

[adityakadam]

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.

[SaiyanJin]

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

[lassestube]

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

[gilneyn.mathias]

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) 😅

[JACKoPL]

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.

[hamed]

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

[CameronAudet]

Is there a way to query "all custom columns" in these examples to show as table headers or would you also make a table to store their names and then fetch them in json or eva? think of an admin needing to add the column "student_id to the student model so all students can have one but it wasn't there at the time of migration, guess I'm still figuring out how to show that table header if it's only ever applies per student and not to the entire student schema

[opensourceandcloud]

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

[umerizharofficial]

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

[zkabadu]

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.

[lucasj.pereira]

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.

[Akosiyawin]

I ddnt even know u can query json column like that

[DevduttaBain]

Is it possible to make relations through json columns?

[vidhyaprakash]

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?