数据库设计 - 我应该使用 30 列还是 1 列包含 JSON/XML 形式的所有数据? [英] Database design - should I use 30 columns or 1 column with all data in form of JSON/XML?

问题描述

我正在做一个项目，需要为业务逻辑存储 30 个不同的字段，稍后将用于为每个字段生成报告

I am doing a project which need to store 30 distinct fields for a business logic which later will be used to generate report for each

30个不同的字段不是一次写入的，业务逻辑有这么多的事务，它会是这样的:

The 30 distinct fields are not written at one time, the business logic has so many transactions, it's gonna be like:

Transaction 1, update field 1-4
Transaction 2, update field 3,5,9
Transaction 3, update field 8,12, 20-30
...
...

N.B 每个事务(都属于一个业务逻辑)将更新任意数量的字段 &没有任何特定的顺序.

N.B each transaction(all belong to one business logic) would be updating arbitrary number of fields & not in any particular order.

我想知道我最好的数据库设计是什么:

I am wondering what's my database design would be best:

1. 在 postgres 数据库中有 30 列代表那 30 个不同的字段.

1. Have 30 columns in postgres database representing those 30 distinct field.

有 30 个以 xml 或 json 形式归档的存储并将其存储在一个中postgres 列.

Have 30 filed store in form of xml or json and store it in just one column of postgres.

1 和 2 哪个更好?

1 or 2 which one is better ?

如果我选择 1>:

我知道编程的角度更容易因为这样我不需要读取整个xml/json并且只更新几个字段然后写回数据库，我只能更新每个事务我需要的几列.

I know for programming perspective is easier Because in this way I don't need to read the overall xml/json and update only a few fields then write back to database, I can only update a few columns I need for each transaction.

如果我选择 2>:

我可以将表重新用于其他用途，因为 blob 列中的内容只是 xml.但是，仅仅因为它有一个存储 xml 的 blob 列就使用泛型表来存储与业务逻辑完全无关的东西是错误的吗?这确实有可能节省创建几个新表的工作量.但是这种重用表的通用想法在 RDBMS 中是错误的吗?

I can potentially generic reuse the table for something else since what's inside the blob column is only xml. But is it wrong to use the a table generic to store something totally irrelevant in business logic just because it has a blob column storing xml? This does have the potential to save the effort of creating a few new table. But is this kind of generic idea of reuse a table is wrong in a RDBMS ?

同样通过选择 2> 似乎我将能够处理潜在的变化，例如更改某些字段/添加更多字段?至少看起来我不需要更改数据库表.但是我仍然需要更改 c++ &内部处理更改的 c# 代码，不确定这是否有任何优势.

Also by choosing 2> it seem I would be able to handle potential change like change certain field /add more field ? At least it seems I don't need to change database table. But I still need to change c++ & c# code to handle the change internally , not sure if this is any advantage.

我在数据库设计方面经验不足，所以我无法决定选择哪一个.任何输入表示赞赏.

I am not experiences enough in database design, so I cannot make the decision which one to choose. Any input is appreciated.

注意，我现在很有可能不需要对这 30 列进行索引或搜索，如果我选择 2>，将在额外的列上创建主键.但我不确定以后是否需要根据这些列/字段中的任何一个进行搜索.

N.B there is a good chance I probabaly don't need to do index or search on those 30 columsn for now, a primary key will be created on a extra column is I choose 2>. But I am not sure if later I will be required to do search based on any of those columns/field.

基本上我所有的字段都是从需求文档中预定义的，他们一般都喜欢简单的字段:

Basically all my fields are predefined from requirement documents, they generally like simple field:

field1: value(max len 10)
field2: value(max len 20)
...
field20: value((max len 2)

没有嵌套字段.是否值得为每个字段创建 20 列(有些是日期/时间等字符串，有些是字符串，有些是整数等).

No nest fields. Is it worth to create 20 columns for each of those fields(some are string like date/time, some are string, some are integer etc).

2>将不同的业务逻辑放在共享表中是一个糟糕的设计理念吗?如果它只是因为它们共享相同的结构而被放在共享表中?例如.它们都有日期时间列、主键和内部具有不同业务逻辑的 xml 列?这样我们就可以节省一些创建新表的工作......这种节省的工作值得做吗?

2> Is putting different business logic in a shared table a bad design idea? If it only being put in a shared table because they share the same structure? E.g. They all have Date time column , a primary key & a xml column with different business logic inside ? This way we safe some effort of creating new tables... Is this saving effort worth doing ?

推荐答案

一般来说，将 JSON 或 XML 文档拆分出来并将其存储为单独的列是明智的.这使您能够对用于验证和检查的列设置约束、为列编制索引、为每个字段使用适当的数据类型，以及通常使用数据库的功能.

In general it's wise to split the JSON or XML document out and store it as individual columns. This gives you the ability to set up constraints on the columns for validation and checking, to index columns, to use appropriate data types for each field, and generally use the power of the database.

将它映射到/从对象通常不会太难，因为有很多工具可以做到这一点.例如，Java 提供 JAXB 和 JPA.

Mapping it to/from objects isn't generally too hard, as there are numerous tools for this. For example, Java offers JAXB and JPA.

拆分它的主要时间不是一个好主意，是当您事先不知道 JSON 或 XML 文档的字段将是什么或其中有多少个时.在这种情况下，您实际上只有两个选择 - 使用 类似EAV的数据模型，或者直接将文档存储为数据库字段.

The main time when splitting it out isn't such a great idea is when you don't know in advance what the fields of the JSON or XML document will be or how many of them there will be. In this case you really only have two choices - to use an EAV-like data model, or store the document directly as a database field.

在这种情况下(仅在这种情况下)我会考虑将文档直接存储在数据库中.PostgreSQL 的 SQL/XML 支持意味着您仍然可以在xpath 表达式，您可以使用触发器进行一些验证.

In this case (and this case only) I would consider storing the document in the database directly. PostgreSQL's SQL/XML support means you can still create expression indexes on xpath expressions, and you can use triggers for some validation.

这不是一个好的选择，只是 EAV 通常是一个更糟糕的选择.

This isn't a good option, it's just that EAV is usually an even worse option.

如果文档是扁平的"——即单层键和值，没有嵌套——考虑将其存储为 hstore，因为 hstore 数据类型更强大.

If the document is "flat" - ie a single level of keys and values, with no nesting - the consider storing it as hstore instead, as the hstore data type is a lot more powerful.

这篇关于数据库设计 - 我应该使用 30 列还是 1 列包含 JSON/XML 形式的所有数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！