如何处理宽平台的列增长-Howtohandlecolumngrowthofwide,flattables

80% of the time, your nulls have definite patterns.

80%的情况下,您的空值具有明确的模式。

These patterns define subclasses of your table. In your case, they will be subclasses of Foreclosure.

这些模式定义了表的子类。在您的情况下,它们将是止赎的子类。

Your splitting should be based on these subclass relationships.

您的拆分应该基于这些子类关系。

Say, for example, some Foreclosure instances have a bunch of fields related to legal proceeding that are nearly all filled in. And other Foreclosure instances have the legal proceeding fields entirely filled with nulls.

比如说,例如,一些止赎实例有一堆与法律诉讼相关的字段几乎全部填写。而其他止赎实例的法律诉讼字段完全填充了空值。

You have two classes. You need to work out the relationship between them -- are they superclass-subclass or are they peer subclasses of some other superclass?

你有两节课。你需要弄清楚它们之间的关系 - 它们是超类 - 子类还是它们是某些其他超类的对等子类?

This tells you how to partition your table to make useful stuff happen.

这告诉您如何对表进行分区以使有用的东西发生。

• You may have proper superclass subclass relationships

  您可能具有适当的超类子类关系

• You may have found a thing (a LegalProceeding) which should have been a separate table all along. It should not have been permanently joined into Foreclosure. This is remarkably common.

  你可能已经找到了一个东西(一个LegalProceeding),它应该一直是一个单独的表。它不应该永久地加入止赎。这非常普遍。

You now have some relational implementation choices.

您现在有了一些关系实现选择。

• One common choice is to put all subclasses into a single, massive table with a lot of nulls. This is what you have today, and it isn't working.

  一个常见的选择是将所有子类放入一个包含大量空值的单个大型表中。这就是你今天所拥有的,它无法正常工作。

• One choice is to split the two subclass relationship tables into peers, duplicating the common information.

  一种选择是将两个子类关系表拆分为对等,复制公共信息。

• One choice is to have a superclass table with an optional FK reference to the additional information in the subclass.

  一种选择是拥有一个超类表,其中包含对子类中其他信息的可选FK引用。

• One choice is to have a subclass table with a mandatory FK reference to the superclass information.

  一种选择是使子类表具有对超类信息的强制FK引用。

Unless you are really brave, app is very small/simple, or there are major performance issues do not fix the schema. If it ain't broke, don't fix it.

除非你真的很勇敢,否则应用程序非常小/简单,或者存在重大性能问题而无法修复架构。如果没有损坏,请不要修理它。

Just create a new table ForeclosureExtended, as you suggest with the same key and start adding columns. Or, you could make proper tables with grouped columns as new columns appear. Either way, if the schema is this bad, I'll bet the code is very fragile.

只需创建一个新表ForeclosureExtended,就像您建议使用相同的密钥并开始添加列。或者,当出现新列时,您可以使用分组列创建正确的表。无论哪种方式,如果架构这么糟糕,我敢打赌代码非常脆弱。

Why do you feel that you have a problem? To my mind it's easier to deal with one table that has a lot of columns than it is to deal with a ton of narrower tables and all the associated views you have to maintain.

为什么你觉得你有问题?在我看来,处理一个包含大量列的表比处理大量较窄的表以及您必须维护的所有相关视图更容易。