数据库设计 - 我应该使用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 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.

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>：

我知道编程透视更容易因为这样， t需要读取整个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。但是，使用一个表通用来存储在业务逻辑中完全不相关的一个表是错误的，因为它有一个blob列存储xml？这确实有潜力节省创建几个新表的努力。但是在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 ++&

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.

NB有一个很好的机会，我probabaly不需要做索引或搜索那些30 columsn现在，将创建一个主键在一个额外的列是我选择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>
将不同的业务逻辑放在一个共享表中一个坏的设计思想？如果它只是放在一个共享表中，因为它们具有相同的结构？例如它们都有Date时间列，一个主键和一个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.

文档是flat - 即单个级别的键和值，没有嵌套 - 考虑将它存储为 hstore 数据类型的功能更强大，因此请改用rel =nofollow> 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屋！