3

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

Rick James
  • 106,233
  • 9
  • 103
  • 171
niko craft
  • 2,393
  • 1
  • 28
  • 48

1 Answers1

3

As long as you don't try to use the properties for searching or sorting, there's not much difference.

As you said, putting a JSON column in your model table allows you to avoid a JOIN to the properties table.

I think your properties table actually needs to have one more column, to name the property. So it should be:

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

The downsides are pretty similar for both solutions.

  • Queries will be more complex with either solution, compared to querying normal columns.

  • Storing non-string values as strings is inefficient. It takes more space to store a numeric or datetime value as a string than as a native data type.

  • You can't apply constraints to the properties. No way to make a property mandatory (you would use NOT NULL for a normal column). No way to enforce uniqueness or foreign key references.

There's one case I can think of that gives JSON an advantage. If one of your custom properties is itself multi-valued, there's a straightforward way to represent this in JSON: as an array within your JSON document. But if you try to use a property table, do you store multiple rows for the one property? Or serialize the set of values into an array on one row? Both solutions feel pretty janky.

Because the "schemaless properties" pattern breaks rules of relational database design anyway, there's not much you can do to "do it right." You're choosing the lesser of two evils, so you can feel free to use the solution that makes your code more convenient.

Bill Karwin
  • 462,430
  • 80
  • 609
  • 762
  • btw "key" names the property. there is also "id" column there if you mistook "key" for being used as "id" :) When it comes to properties table, 1 property = 1 row only, where I save for example in a single row these values: key: "showSomeSetting", value: true, type: boolean Problem here is if I have 40 properties which I can easily have I get 40 rows and each property value column as you know can be of type "string", "integer", "json" or "boolean" and I have to manually convert them to those once I get relationship back from db – niko craft Apr 25 '19 at 17:18
  • >Queries will be more complex with either solution, compared to querying normal columns.< I wíll not query models based on their properties, I just use properties or settings if we call them that, to decide how to render data on the frontend, some of these properties are just pure css settings, like padding, margin, etc. There is no situation where I would be getting bunch of rows for a particular model based on its padding or margin or any other property, so querying by a property will not be happening in my application. – niko craft Apr 25 '19 at 17:20
  • >Storing non-string values as strings is inefficient. It takes more space to store a numeric or datetime value as a string than as a native data type.< I know, but if I only want to have 1 property = 1 row, I have to store all values inside a string column in my case named "value" and that is why I also have type column to tell me how to convert it back. With Json approach I do not have to worry about this, json stores strings, numbers, etc correctly, I do not have to cast it back to these types from string like I have to when using properties table design pattern – niko craft Apr 25 '19 at 17:22
  • >You can't apply constraints to the properties. No way to make a property mandatory< I can't on the backend, but I can on the frontend in the admin area via not letting the user save unless all properties that I've marked mandatory are filled. Ofc user could use postman to insert a null property, but this would be admin of a website, if he wants to "hack" his own website by using something like a postman to insert a null value I'm ok with that, the Admin interface itself would prevent him to do that since I can do validation on frontend as well. – niko craft Apr 25 '19 at 17:24
  • >If one of your custom properties is itself multi-valued> I have a type for that, I save it as json type in 1 row. And I have to cast it back to json manually after I retrieve the relationship and go through all the properties again to see what type they are. There are no problems here I have working code and is fast. But it just does not feel as elegant as If I just used one json column, then I do not have to loop through all properties check the type and cast them correctly to their correct type. – niko craft Apr 25 '19 at 17:29
  • I appreciate your thoughts and feedback, I will not mark it as a correct answer just yet, I'd like to hear more opinions, if there are none by tomorrow I'll select your answer as correct! – niko craft Apr 25 '19 at 17:30
  • >You're choosing the lesser of two evils, so you can feel free to use the solution that makes your code more convenient.< Agreed! :) – niko craft Apr 25 '19 at 17:31