sql - json 是否适合保存来自动态列的值
问题描述
json 是否适合保存来自动态列的值?我想在事先知道结构的地方存储有关文档的数据,例如名称、名称、年份、地点等。现在,我已将这些字段定义为 MS SQL 表中的列。但是,我想存储有关“动态”文档的数据,用户自己将从某个池中选择一个字段并插入一个值。现在,我将这些数据作为 json 存储在一列中。例如,一次将是这样的:'{"Name":"John Doe","Place":"Chicago"}'
而其他时候只能是:'{"Name": "John Doe"}'
我想知道如何统一这些数据。我认为来自第一类文档(具有已知列数)的数据也应该存储在 json 中。但我不知道这是否是处理大量数据(例如 100,000 条记录)的好方法。
解决方案
首先,100,000 行并不是很多列。您甚至在谈论千兆字节的数据是值得怀疑的。
XML 和 JSON 都会产生在数据中存储字段名称的开销。如果您有很多重复的字段名称,那么就会存储大量冗余的字段名称。更大的行会减慢查询速度。
JSON 和 XML 在验证字段名称方面也存在挑战。这可以通过应用程序或约束来处理。也就是说,当属性很少使用时,它们会非常有用。
您的示例数据只是建议 NULLable 列。你可以拥有name
和place
。如果没有,place
则值为NULL
。鉴于您有一个固定的池,有一个很好的变化是这种结构是最简单和最有效的。唯一的缺点是添加新列需要向表中添加列。这可能是一项昂贵的操作。
另一种方法是 EAV 模型,评论中提到了它。这解决了重复名称的问题,因为您可以使用 ids 代替。所以,你可以有:
create table optionalFields (
optionFieldId int identity(1, 1) primary key,
name varchar(255)
);
create table userOptionalFields (
userOptionalFieldId int identity (1, 1) primary key,
userId int references users(userId),
optionalFieldId int references optionalFields(optionalFieldId),
value varchar(255)
);
EAV 模型的缺点是当所有值都是字符串时它最简单,如果某些值是数字或日期,这可能会有点棘手。从积极的方面来说,数据库确保这些字段是有效的。
不同数据模型之间的选择取决于以下因素:
- 值的数据类型。
- 字段总数。
- 添加新字段的频率。
- 字段(对于给定用户)是否更新,如果是,它们是逐个更新还是一次性更新。
- 给定用户的常用字段。
- 您对 XML 和 JSON 的熟悉程度。
- 字段名称是否有同义词(例如“名称”的“全名”)。
- 字段名称是否会更改。例如,“Name”会不会突然变成“FullName”?
毫无疑问还有其他问题。