jeudi 25 avril 2019

properties table pattern vs storing all properties in json column

I'd like some feedback on having all properties a model can have in a properties table accessed via relationship (using laravel relationships) vs storing all properties/settings in the same table but in a json column.

Currently, my application has a propeties table called settings that is also polymorphic in nature so multiple models can store their properties there. This table has columns like

key (string), 
value(string), 
type (string) - tells if the value is of string, integer, boolean, json type 

so that I do not send strings to javascript frontend, but instead I can send string, integer, boolean native types for better handling of types in frontend. I do this conversion before I send the properties to the frontend using php function that cast string values to int, boolean, json or string, depending on the type.

This means if a model has 40 properties, all get stored in its own row, so creating one model leads to creating 40 rows that store all properties it may have.

Now the above approach vs approach where I just have a single json column, we can call it settings and I dump all these 40 properties there.

What do I win with json column approach? I shave off a table and I shave off an extra relationship that I need to load on this model each time I do some queries. I also shave off having to each time I get properties cast them to integer, boolean, json or string. (remember the type column above) To keep in mind these properties do not need to be searchable, I need them only for reading from them. I will never use them in queries to return posts based on these properties.

Which one is a better idea to use, I'm building a CMS btw you can see it in action here: https://www.youtube.com/watch?v=pCjZpwH88Z0

Aucun commentaire:

Enregistrer un commentaire