属性表模式vs将所有属性存储在json列中 [英] properties table pattern vs storing all properties in json column

问题描述

相对于通过关系(使用laravel关系)访问的属性表中存储模型可以拥有的所有属性，还是将所有属性/设置存储在同一表中但在json列中，我希望获得一些反馈.

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 

以便我不将字符串发送到javascript前端，而是可以发送字符串，整数，布尔型本机类型，以便更好地处理前端中的类型.我先进行此转换，然后使用php函数将属性发送到前端，该函数根据类型将字符串值转换为int，boolean，json或string.

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.

这意味着如果一个模型有40个属性，则所有属性都存储在其自己的行中，因此创建一个模型将导致创建40个行，以存储其可能具有的所有属性.

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.

现在上面的方法vs方法中，我只有一个json列，我们可以将其称为设置，然后将所有这40个属性都转储到那里.

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.

使用json列方法能赢什么?我删除了一个表，并且删除了每次执行一些查询时都需要在此模型上加载的额外关系.我也不必每次都将属性强制转换为整数，布尔值，json或字符串. (记住上面的类型列)要记住这些属性不需要可搜索，我只需要从它们中读取即可.我绝不会在查询中使用它们来基于这些属性返回帖子.

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.

使用哪个更好的主意，我正在构建一个CMS，您可以在这里看到它的运行情况: https://www.youtube.com/watch?v=pCjZpwH88Z0

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

推荐答案

只要您不尝试使用属性进行搜索或排序，就没有太大区别.

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

正如您所说，在模型表中放置一个JSON列可以避免加入属性表.

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.

您不能将约束应用于属性.无法使属性成为强制性(对于常规列，您将使用NOT NULL).无法强制唯一性或外键引用.

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.

我可以想到一种情况，它使JSON具有优势.如果您的自定义属性之一本身就是多值的，则有一种直接的方法可以在JSON中表示此值:作为JSON文档中的数组.但是，如果您尝试使用属性表，则是否为一个属性存储多行?还是将一组值序列化为一行中的数组?两种解决方案都感觉很麻烦.

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.

这篇关于属性表模式vs将所有属性存储在json列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！