管理数据库引用数据及其相关代码
参考数据或查找表是诸如 CustomerType、ProductType 等。 它们很少变化，偶尔会添加新类型或旧类型退役。 在代码中，它们通常被复制为枚举/常量，也用于填充组合框。 添加新类型不应破坏现有应用程序，并且通常情况下，这些新类型仅需要支持新应用程序的功能，旧应用程序应忽略它。
这种情况在大多数开发商店中都很常见，几年/几个月后，它会变得混乱、不受控制，如果数据库和代码不同步，就会发生糟糕的事情。
其他人如何处理这个问题？ 代码/数据库是什么样的？它的版本如何？
Reference Data, or lookup tables are things like CustomerType, ProductType and so on. They change infrequently, occasionally a new type is added or an old one retired. In code they're often reproduced as enums/constants, and also used to populate combo boxes. Adding a new Type shouldn't break existing applications, and more often than not those new types are only required to support a feature of a new application, the legacy app(s) should ignore it.
This situation will be familiar in most dev shops, after a few years/months it's messy, uncontrolled and, if the DB and code gets out of step, bad things happen.
How do others manage this issue? What does the code/DB look like, and how is it versioned?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您的意思是，ID 和标签被硬编码在代码中，并且也出现在数据库的查找表中？
我们采取的方法是从数据库中读取“类型”ID 和标签，并使用它们来填充列表框。
（幸运的是）我不必支持需要从同一查找表读取不同值集的应用程序的不同版本。
我听说有人将最小版本 ID 分配给查找表值。 应用程序传入其版本（可能是 1.5），并检索版本 1.5 或更低版本的所有查找值。 为应用程序的更高版本（例如 2.1）添加的查找值将被忽略。
这显然会带来一些显着的维护开销。
Do you mean, that IDs and labels are hard-coded in the code, and appear in a look-up table in the database as well?
The approach we've taken is to read the 'type' IDs and labels from the database and use them to populate list boxes.
(Fortunately) I've not had to support different versions of an application that need to read different sets of values from the same look-up table.
I've heard of people assigning minimum version IDs to look-up table values. The application passes in its version (1.5 maybe), and retrieves all look-up values with a version 1.5 or less. Look-up values added for a later version of the application (e.g. 2.1) would be ignored.
This obviously introduces some significant maintenance overhead.
我们创建了一种自行开发的工具，该工具在构建过程中运行一个或多个文件中指定的查询，并生成枚举类型类，每个引用数据表对应一个枚举类型类。 该工具至少只需要参考数据表具有两列：主键（具有唯一约束）和字符串。 每个枚举实例都有一个从字符串生成的名称（通过算法将名称转换为大写、将空格和其他无效字符替换为下划线等）。
该工具足够灵活，可以为每个值添加附加属性； 例如，“显示名称”、“描述”、可能关联的数值以及其他简单类型。 我们还在枚举类上生成静态方法来获取各种值的子集； 总是至少有一个返回所有值，但我们可以根据 SQL 查询生成其他值。 例如，对于颜色枚举，我们可能有一个“primaryColors()”静态方法。 生成额外的静态方法来根据键查找值； 例如，
枚举使在代码中使用众所周知的参考值变得容易且更具可读性； 例如，
这确实有一个缺点，需要额外的构建步骤，但在我们的例子中，它的优点远远超过了它：更干净的代码，保证 UI、业务逻辑和数据库有效值同步等。
我们经常谈到了如上所述的静态机制的混合加上添加更多的动态行为，但我们实际上从未觉得它有足够的吸引力来增加复杂性。
We created a home-grown tool that runs queries specified in one or more files during the build and generates enumerated type classes, one for each reference data table. At a minimum the tool only requires reference data tables to have two columns: a primary key (with unique constraints) and a string. Each enum instance has a name generated from the string (after getting munged through an algorithm to convert the name into upper case, replace whitespace and other invalid characters to underscores, etc.).
The tool is flexible enough to allow for additional properties on each value; e.g., a "display name", a "description", maybe associated numeric values, and other simple types. We also generate static methods on the enum class to get various subsets of values; there's always at least one that returns all values, but we can have additional ones generated based on SQL queries. For a color enum, we might have a "primaryColors()" static method, for example. Additional static methods are generated to look up a value based on its key; e.g.,
The enums make it easy and more readable to use well-known reference values in code; e.g.,
This does have a disadvantage of requiring an additional build step, but in our case that's far outweighed by the advantages: cleaner code, assurance that the UI, business logic, and database valid values are in sync, etc.
We've frequently talked about having a hybrid of a static mechanism as described above plus adding more dynamic behavior, but we've never actually felt it compelling enough to add to the complexity.