数据库设计有助于改变模式

I work for a billing service that uses some complicated mainframe-based billing software for it's core services. We have all kinds of codes we set up that are used for tracking things: payment codes, provider codes, write-off codes, etc... Each type of code has a completely different set of data items that control what the code does and how it behaves.

我为一个帐单服务工作，它的核心服务使用一些复杂的基于大型机的帐单软件。我们有各种各样的代码用于跟踪事情:支付代码，提供程序代码，注销代码等等…每种类型的代码都有一组完全不同的数据项，这些数据项控制代码的行为和行为。

I am tasked with building a new system for tracking changes made to these codes. We want to know who requested what code, who/when it was reviewed, approved, and implemented, and what the exact setup looked like for that code. The current process only tracks two of the different types of code. This project will add immediate support for a third, with the goal of also making it easy to add additional code types into the same process at a later date. My design conundrum is that each code type has a different set of data that needs to be configured with it, of varying complexity. So I have a few choices available:

我的任务是建立一个新的系统来跟踪对这些代码所做的更改。我们想知道谁请求了什么代码，谁/什么时候检查、批准和实现了代码，以及该代码的确切设置是什么样子。当前进程只跟踪两种不同类型的代码。这个项目将增加对第三个项目的直接支持，目标是在以后的时间内将额外的代码类型添加到相同的流程中。我的设计难题是，每种代码类型都有一组不同的数据需要配置，它们的复杂性不同。所以我有几个选择:

I could give each code type it's own table(s) and build them independently. Considering we only have three codes I'm concerned about at the moment, this would be simplest. However, this concept has already failed or I wouldn't be building a new system in the first place. It's also weak in that the code involved in writing generic source code at the presentation level to display request data for any code type (even those not yet implemented) is not trivial.

我可以为每个代码类型提供它自己的表，并独立地构建它们。考虑到我们现在只关心三个代码，这将是最简单的。然而，这个概念已经失败了，否则我不会首先构建一个新的系统。在表示层编写通用源代码以显示任何代码类型(甚至那些尚未实现的)的请求数据时所涉及的代码也不是那么简单。
Build a db schema capable of storing the data points associated with each code type: not only values, but what type they are and how they should be displayed (dropdown list from an enum of some kind). I have a decent db schema for this started, but it just feels wrong: overly complicated to query and maintain, and it ultimately requires a custom query to view full data in nice tabular for for each code type anyway.

构建一个db模式，它能够存储与每个代码类型相关联的数据点:不仅是值，还包括它们是什么类型以及如何显示它们(从某种类型的枚举中下拉列表)。我有一个不错的db模式，但它只是感觉不对:过于复杂，难以查询和维护，而且它最终需要一个自定义查询来查看每个代码类型的良好列表中的完整数据。
Storing the data points for each code request as xml. This greatly simplifies the database design and will hopefully make it easier to build the interface: just set up a schema for each code type. Then have code that validates requests to their schema, transforms a schema into display widgets and maps an actual request item onto the display. What this item lacks is how to handle changes to the schema.

将每个代码请求的数据点存储为xml。这极大地简化了数据库设计，并希望使构建接口更容易:只需为每个代码类型设置一个模式。然后让代码验证对其模式的请求，将模式转换为显示小部件，并将实际的请求项映射到显示中。这个项目缺少的是如何处理模式的更改。

My questions are: how would you do it? Am I missing any big design options? Any other pros/cons to those choices?

我的问题是:你会怎么做?我是否遗漏了一些大的设计选项?对这些选择还有其他的赞成或反对意见吗?

My current inclination is to go with the xml option. Given the schema updates are expected but extremely infrequent (probably less than one per code type per 18 months), should I just build it to assume the schema never changes, but so that I can easily add support for a changing schema later? What would that look like in SQL Server 2000 (we're moving to SQL Server 2005, but that won't be ready until after this project is supposed to be completed)?

我目前倾向于使用xml选项。考虑到模式更新是意料之中的，但是非常不频繁(可能每18个月每个代码类型只更新一次)，我是否应该构建它，假设模式从未更改过，但是这样以后我就可以轻松地添加对更改模式的支持?在SQL Server 2000中会是什么样子(我们将转移到SQL Server 2005，但是在这个项目应该完成之前还没有准备好)?

[Update]:
One reason I'm thinking xml is that some of the data will be complex: nested/conditional data, enumerated drop down lists, etc. But I really don't need to query any of it. So I was thinking it would be easier to define this data in xml schemas.

[Update]:我认为xml的一个原因是，有些数据将是复杂的:嵌套/条件数据、枚举下拉列表等等。所以我想用xml模式来定义这些数据会更容易些。

However, le dorfier's point about introducing a whole new technology hit very close to home. We currently use very little xml anywhere. That's slowly changing, but at the moment this would look a little out of place.

然而，le dorfier关于引进一项全新技术的观点在国内很受欢迎。目前我们在任何地方都很少使用xml。这种情况正在慢慢改变，但现在看起来有点不太合适。

I'm also not entirely sure how to build an input form from a schema, and then merge a record that matches that schema into the form in an elegant way. It will be very common to only store a partially-completed record and so I don't want to build the form from the record itself. That's a topic for a different question, though.

我也不完全确定如何从模式构建输入表单，然后以一种优雅的方式将匹配该模式的记录合并到表单中。通常只存储部分完成的记录，所以我不想从记录本身构建表单。这是另一个问题的主题。

Based on all the comments so far Xml is still the leading candidate. Separate tables may be as good or better, but I have the feeling that my manager would see that as not different or generic enough compared to what we're currently doing.

基于到目前为止所有的评论，Xml仍然是主要的候选者。分开的表格可能是好的或者更好的，但是我感觉我的经理会认为这和我们目前所做的相比并没有太大的不同和共性。

3 个解决方案

#1

There is no simple, generic solution to a complex, meticulous problem. You can't have both simple storage and simple app logic at the same time. Either the database structure must be complex, or else your app must be complex as it interprets the data.

对于一个复杂、细致的问题，没有简单、通用的解决方案。你不可能同时拥有简单的存储和简单的应用程序逻辑。数据库结构必须是复杂的，或者应用程序在解释数据时必须是复杂的。

I outline five solution to this general problem in "product table, many kind of product, each product have many parameters."

我在“产品表，多种产品，每种产品都有很多参数”中概述了解决这一普遍问题的五种方法。

For your situation, I would lean toward Concrete Table Inheritance or Serialized LOB (the XML solution).

对于您的情况，我倾向于使用具体的表继承或序列化的LOB (XML解决方案)。

The reason that XML might be a good solution is that:

XML可能是一个好的解决方案的原因是:

You don't need to use SQL to pick out individual fields; you're always going to display the whole form.
您不需要使用SQL来挑选单个字段;你总是要显示整个表单。
Your XML can annotate fields for data type, user interface control, etc.
您的XML可以注释数据类型、用户界面控件等字段。

But of course you need to add code to parse and validate the XML. You should use an XML schema to help with this. In which case you're just replacing one technology for enforcing data organization (RDBMS) with another (XML schema).

当然，您需要添加代码来解析和验证XML。您应该使用XML模式来帮助实现这一点。在这种情况下，您只需将一种技术替换为另一种技术(XML模式)来强制执行数据组织(RDBMS)。

You could also use an RDF solution instead of an RDBMS. In RDF, metadata is queriable and extensible, and you can model entities with "facts" about them. For example:

您还可以使用RDF解决方案而不是RDBMS。在RDF中，元数据是可查询和可扩展的，您可以用“事实”对实体进行建模。例如:

Payment code XYZ contains attribute TradeCredit (Net-30, Net-60, etc.)
支付代码XYZ属性包含TradeCredit (Net-30, Net-60等)
Attribute TradeCredit is of type CalendarInterval
属性TradeCredit是calendar类型的
Type CalendarInterval is displayed as a drop-down
类型CalendarInterval显示为下拉列表
.. and so on
. .等等

Re your comments: Yeah, I am wary of any solution that uses XML. To paraphrase Jamie Zawinski:

回复您的评论:是的，我对任何使用XML的解决方案都持谨慎态度。套用Jamie Zawinski:

Some people, when confronted with a problem, think "I know, I'll use XML." Now they have two problems.

有些人在遇到问题时，会想:“我知道，我会使用XML。”现在他们有两个问题。

Another solution would be to invent a little Domain-Specific Language to describe your forms. Use that to generate the user-interface. Then use the database only to store the values for form data instances.

另一种解决方案是发明一种特定于领域的语言来描述表单。使用它生成用户界面。然后，仅使用数据库存储表单数据实例的值。

#2

Why do you say "this concept has already failed or I wouldn't be building a new system in the first place"? Is it because you suspect there must be a scheme for handling them in common?

为什么你会说“这个概念已经失败了，否则我根本就不会建立一个新的系统”?是因为你怀疑一定有一个共同的方案来处理它们吗?

Else I'd say to continue the existing philosophy, and establish additional tables. At least it would be sharing an existing pattern and maintaining some consistency in that respect.

否则，我将继续现有的哲学，并建立其他表。至少，它将共享一个现有的模式，并在这方面保持一定的一致性。

#3

Do a web search on "generalized specialized relational modeling". You'll find articles on how to set up tables that store the attributes of each kind of code, and the attributes common to all codes.

在“通用专门化关系建模”上进行web搜索。您将看到关于如何设置表来存储每种代码的属性以及所有代码的公共属性的文章。

If you’re interested in object modeling, just search on “generalized specialized object modeling”.

如果您对对象建模感兴趣，只需搜索“通用专门化对象建模”。

#1