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

[@javierrenteria3195]

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

[@benlaceyseniordev]

I was wondering if the JSON column could be indexed or cached. I know indexing numbers is quicker than strings, hence the pivot table. In my experience multiple pivot tables can make the query harder to write and adds latency vs adding in new columns to a table. If the columns are temporary then I may be tempted to call the column tmp_product_size or whatever with a database column description saying why that is used, when it was added and have a review date. If the field isn't used then remove it and refactor the code or keep it and rename the column to product_size. I would be more likely to just add a new column if its one value being stored. If there are multiple then I may store an array or use JSON in that case.

[@pekorabestgirl543]

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

[@binbashbuddy]

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.

[@wgblondel]

I feel like you just described the company I've been working for since July. All the issues you mentioned, we have them too haha.

[@saytaimoor]

And I thought I was the only one who see these kind of blunders everywhere while everyone else seem to casually do it again and again. I can feel your pain.

[@DianaKriese]

Thanks for sharing your situation. I can relate also.

[@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.

[@adityakadam2256]

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.

[@AMoktar]

Awesome bro, thanks ❤

[@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

[@SaiyanJin85]

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

[@LaravelDaily]

Good point, probably.

[@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

[@lucasj.pereira4912]

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.

[@LaravelDaily]

There's no "good practice" or "bad practice", do whatever works for you, seems it's working well for you so all good.

[@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

[@kennedymwenda3357]

Is database engine PostgreSQL?

[@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.

[@LaravelDaily]

Adding new values to enum requires changes in the code, if it's a separate table then anyone can do it via adminpanel

[@zkabadu]

@@LaravelDaily okay, fair enough. But if it's something that changes along with the code and not everybody can add new properties than it would be my way to go. Anyway, great to see you have a look at all your videos and keep replying to new comments :)

[@DevduttaBain]

Is it possible to make relations through json columns?

[@LaravelDaily]

No

[@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.

[@gilney.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) 😅

[@jdbt7874]

Did you see it as a bad approach?

[@hamed334]

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

[@cellocarlos]

Where is the link to the article?

[@LaravelDaily]

Sorry :) laraveldaily.com/post/laravel-custom-fields-json-eav-model-same-table

[@vidhyaprakash85]

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?

[@LaravelDaily]

I'm not sure, is it possible? But generally yes you can restructure that whereHas query in a different way, with joins maybe

[@maflones]

@@LaravelDaily Try it with clean syntax in RawSQL and you'll be surprised.

[@Akosiyawin]

I ddnt even know u can query json column like that

[@praffulpanwar]

[@mayanksgajjar]

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