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

hello guys if you have a database structure which is unclear yet and you envision more columns added in the future but you don't know which columns a typical example is an eShop with some properties for the products and they need to be flexible what database structure to choose I have written an article on laravel dailycom with these three options discussed and also Benchmark the performance and I decided to shoot a video version here so imagine three options and I've created all of them in one database so Json column for the properties also separate Fields some of them knowable and then you would add more Fields as more properties appear and then the third option is to have pivot table product property with the value and properties separately in the database separate table which is basically called eav Model entity attribute value model from what I remember so let's compare those I've seeded some data not too much so 1000 product works with random combinations of those values and then in my Tinker well I've launched The Benchmark for querying the product by a few filters so for example you need products with size L and color red if you have a Json field it is very convenient to query if you have just regular fields in the database they are even easier to query but if you have eav model with the relationships then it gets a bit more complicated the query and I'm launching that with laravel Benchmark functionality which allows you in the array compare three functions and the number five means launch that five times and let's do exactly that in Tinker well and what is the result so let's see 28 milliseconds for the eav model five millisecond for Json and of course the fastest is just query the fields let's launch it once again maybe some caching or something yeah it is a bit different let's launch again to make sure again a few more times this is interesting so those numbers for one and two seem to jump which one is faster so it seems like Json is as fast as querying the fields in that situation in that simple database structure with just thousand products but a clear loser in terms of performance here is this whereas with the flexible model now of course every Benchmark is a little bit biased so probably in your situations in your products structure The Benchmark result could be a bit different but this is just an example for you to think about and from that what could be the takeaways so you need to choose whether you go for flexibility or for performance for smaller amounts of data for example if I try to do that for laravel dailycom for the articles I know there will be only like thousand articles or a few thousand articles ever in my lifetime so so I could go for flexible properties and then ignore a bit of performance because 30 milliseconds or two milliseconds the users would not actually feel that in the browser that difference the Json thing it seems okay until it becomes more complicated I've read by other people online and on layercast form quite a few articles where Json fields are really hard to query so this is very easy to query the size and the color but if you have Json with a few levels for example or you need to do like where Json contains something it may be much slower and then the Third Kind of a silly option to just add more columns it seems like a not a real developer example because we all go for flexibility and maintainability but in fact in real life multiple times I've seen clients going we need to be flexible I need to add more fields on columns and something and guess what they rarely use that at all so often developers over engineer and try to come up with complex system although in fact in all the life cycle of the project they would ever need to add two more fields which they can do manually when it's needed so in some cases it's better not to over complicate the project and if you do feel that more columns are added or needed constantly then you refactor that into Json or eav model what do you think what is your expertise and experience on those options have you ever burned Yourself by choosing the wrong approach and then you had to refactor or optimize share your stories in the comments and if you care about performance in general eloquent performance I can suggest my separate course which is if we scroll down better eloquent performance inside of that I show a lot of queries optimized and non-optimized that course is a part of membership on laravel dailycom so subscribe to yearly or monthly membership to get all of the courses including that eloquent performance one and see you guys in other videos

Channel: Laravel Daily

Views: 13,902

Keywords:

Id: jkKVy5UQ6Y0

Length: 5min 5sec (305 seconds)

Published: Tue Feb 07 2023