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 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.
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.
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
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.
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.
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
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.
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 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) 😅
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 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 :)
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?
as you said it depends on what you are going do.. hard to decide! thanks for this video :)
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 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.
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.
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
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.
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.
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
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
Awesome bro, thanks ❤
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
Good point, probably.
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.
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 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) 😅
Did you see it as a bad approach?
WhereHas performance is bad.
It could be a good idea to do the first Query using joins.
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.
There's no "good practice" or "bad practice", do whatever works for you, seems it's working well for you so all good.
Is database engine PostgreSQL?
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.
Adding new values to enum requires changes in the code, if it's a separate table then anyone can do it via adminpanel
@@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 :)
I ddnt even know u can query json column like that
Is it possible to make relations through json columns?
No
Where is the link to the article?
Sorry :) laraveldaily.com/post/laravel-custom-fields-json-eav-model-same-table
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?
I'm not sure, is it possible? But generally yes you can restructure that whereHas query in a different way, with joins maybe
@@LaravelDaily Try it with clean syntax in RawSQL and you'll be surprised.
I mostly prefer the columns and JSON way which is more easy and convenient.