处理查找表和相关业务逻辑时摆脱硬编码值
示例案例：
我们正在使用 SQL Server 构建租赁服务。有关可租用物品的信息存储在表中。每个项目都有一个状态，可以是“可用”、“已租”或“损坏”。不同的状态驻留在查找表中。
ItemState表：
id名称
1 '可用'
2“租用”
3“损坏”
除此之外，我们还有一条业务规则，规定每当返回一个项目时，它的状态就会从“已租”更改为“可用”。
这可以通过诸如“update Items set state=1 where id=@itemid”之类的更新语句来完成。在应用程序代码中，我们可能有一个映射到 ItemState id:s 的枚举。但是，它们包含硬编码值，可能会导致以后出现维护问题。假设开发人员要更改状态集但忘记修复相关的业务逻辑层...
有什么好的方法或替代设计来处理此类设计问题？
除了直接答案之外，还欢迎相关文章的链接。
Example case:
We're building a renting service, using SQL Server. Information about items that can be rented is stored in a table. Each item has a state that can be either "Available", "Rented" or "Broken". The different states reside in a lookup table.
ItemState table:
id name
1 'Available'
2 'Rented'
3 'Broken'
Adding to this we have a business rule which states that whenever an item is returned, it's state is changed from "Rented" to "Available".
This could be done with a an update statement like "update Items set state=1 where id=@itemid". In application code we might have an enum that maps to the ItemState id:s. However, these contain hard coded values that could lead to maintenance issues later on. Say if a developer were to change the set of states but forgot to fix the related business logic layer...
What good methods or alternate designs are there for dealing with this type of design issues?
Links to related articles are also appreciated in addition to direct answers.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（10）
根据我的经验，在这种情况下，您实际上必须进行硬编码，最好使用整数值与查找表的 id 相匹配的枚举。我认为“1”总是“可用”等等并没有什么错。
In my experience this is a case where you actually have to hardcode, preferably by using an Enum which integer values match the id's of your lookup tables. I can't see nothing wrong with saying that "1" is always "Available" and so forth.
我见过的大多数系统都对查找表值进行硬编码并接受它。这是因为，在实践中，代码表很少会像您想象的那样发生变化。如果它们确实发生了变化，您通常需要重新编译任何依赖该 DDL 的程序。
也就是说，如果您想让代码可维护（一个值得称赞的目标），最好的方法是将这些值外部化到属性文件中。然后您可以稍后编辑此文件，而无需重新编码整个应用程序。
这里的限制因素是您的应用程序自身的内部状态取决于您从查找表中获取的值，因此这意味着一定程度的耦合。
对于应用不依赖该代码的查找（例如，如果您的代码表存储在地址下拉列表中使用的两个字母的状态代码列表），那么您可以将代码延迟加载到对象中，并仅在需要时访问它们。但这对你正在做的事情不起作用。
Most systems that I've seen hard code the lookup table values and live with it. That's because, in practice, code tables rarely change as much as you think they might. And if they ever do change, you generally need to re-compile any programs that rely on that DDL anyway.
That said, if you want to make the code maintainable (a laudable goal), the best approach would be to externalize the values into a properties file. Then you can edit this file later without having to re-code your entire app.
The limiting factor here is that your app depends for its own internal state on the value you get from the lookup table, so that implies a certain amount of coupling.
For lookups where the app doesn't rely on that code, (for instance, if your code table stores a list of two-letter state codes for use in an address drop-down), then you can lazily load the codes into an object and access them only when needed. But that won't work for what you're doing.
当您在代码中定义查找表和枚举时，您总是会遇到保持它们同步的问题。这里无能为力。两者都有效地生活在两个不同的世界中，并且通常彼此不了解。
您可能希望拒绝使用查找表，而只让您的业务逻辑操作这些值。在这种情况下，您将错过依靠引用完整性来支持数据完整性的选项。
另一种选择是以您在代码中永远不需要这些值的方式构建应用程序。这意味着将部分业务逻辑移动到数据库层，也就是说，将它们放入存储过程和触发器中。这还有一个好处是对客户来说是不可知的。任何人都可以调用 SP 并确保数据将保持一致状态，也与您的业务逻辑规则一致。
When you have your lookup tables as well as enums defined in the code, then you always have an issue with keeping them in sync. There is not much that can be done here. Both live effectively in two different worlds and are generally unaware of each other.
You may wish to reject using lookup tables and only let your business logic operate these values. In that case you miss the options of relying on referential integrity to back you ap on the data integrity.
The other option is to build up your application in that way that you never need these values in your code. That means moving part of your business logic to the database layer, meaning, putting them in stored procedures and triggers. This will also have the benefit of being agnostic to the client. Anyone can invoke SPs and get assured the data will be kept in the consistence state, consistent with your business logic rules as well.
您需要有一些永远不会改变的预定义值，无论是整数、字符串还是其他值。
在您的情况下，状态的数值是状态的代理
PRIMARY KEY，在设计良好的数据库中它永远不会改变。如果您担心一致性，请使用
CHAR代码：A、R或B。但是，您应该坚持使用它以及数字代码，以便
A始终表示Available等。您的数据库结构应该与代码一样记录下来。
You'll need to have some predefined value that never changes, be it an integer, a string or something else.
In your case, the numerical value of the state is the state's surrogate
PRIMARY KEYwhich should never change in a well-designed database.If you're concerned about the consistency, use a
CHARcode:A,RorB.However, you should stick to it as well as to a numerical code so that
Aalways meansAvailableetc.You database structure should be documented as well as the code is.
答案完全取决于您使用的语言：Java、PHP、Smalltalk 甚至 Assembler 中的解决方案都不相同...
但是让我告诉您一些事情：虽然真正的硬编码值并不是一件好事，有时您确实需要它们。这几乎就是其中之一：您需要在代码中声明您当前对业务逻辑的了解，其中包括这些硬编码状态。
因此，在这种特殊情况下，我会对这些值进行硬编码。
The answer depends entirely on the language you're using: solutions for this are not the same in Java, PHP, Smalltalk or even Assembler...
But let me tell you something: while it's true hard coded values are not a great thing, there are times in which you do need them. And this one is pretty much one of them: you need to declare in your code your current knowledge of the business logic, which includes these hard coded states.
So, in this particular case, I would hard code those values.
不要过度设计它。在尝试解决这个问题之前，您需要弄清楚它是否是一个问题。您能想到任何合法的假设场景，您可以更改 itemState 表中的值吗？不仅仅是“如果有人改变这张桌子怎么办？”但是“有人想出于 Y 的原因以 X 的方式更改此表，这会产生什么影响？”。你需要保持现实。
新状态？您添加一行，但不会影响现有行。
删除一个状态？无论如何，您都必须删除代码中对它的引用。
改变状态的id？没有正当理由这样做。
更改州名？没有正当理由这样做。
所以确实没有理由担心这个。但是，如果您必须在非理性的人随机决定将可用值更改为 2 的情况下进行干净的维护，因为它更适合他们的风水，请确保所有表都是通过从配置文件中读取这些值的脚本生成的，并且然后确保所有代码都从同一配置文件读取常量。然后，您就有一个定义位置，并且任何时候您想要更改值时，您都可以修改该配置文件而不是数据库/代码。
Don't overdesign it. Before trying to come up with a solution to this problem, you need to figure out if it's even a problem. Can you think of any legit hypothetical scenario where you would change the values in the itemState table? Not just "What if someone changes this table?" but "Someone wants to change this table in X way for Y reason, what effect would that have?". You need to stay realistic.
New state? you add a row, but it doesn't affect the existing ones.
Removing a state? You have to remove the references to it in code anyway.
Changing the id of a state? There is no legit reason to do that.
Changing the name of a state? There is no legit reason to do that.
So there really should be no reason to worry about this. But if you must have this cleanly maintainable in the case of irrational people who randomly decide to change Available to 2 because it just fits their Feng Shui better, make sure all tables are generated via a script which reads these values from a configuration file, and then make sure all code reads constants from that same configuration file. Then you have one definition location and any time you want to change the value you modify that configuration file instead of the DB/code.
我认为这是一个常见问题，也是一个合理的担忧，这就是为什么我首先在谷歌上搜索并找到这篇文章的原因。
如何创建一个公共静态类来保存所有查找值，而不是硬编码，而是在加载应用程序时初始化这些值并使用名称来引用它们？
在我的应用程序中，我们尝试了这个，它有效。您还可以进行一些检查，例如，代码中查找的不同可能值的数量应该与数据库中的相同，如果不是，则记录/电子邮件/等。但我不想为 40 多个商业实体的状态手动编码。
此外，这可能是 OR 映射更大问题的一部分。我们暴露了持久层的太多细节，因此我们必须照顾它。使用实体框架等技术，我们不需要担心“同步”部分，因为它是自动化的，对吗？
谢谢！
I think this is a common problem and a valid concern, that's why I googled and found this article in the first place.
What about creating a public static class to hold all the lookup values, but instead of hard-coding, we initialize these values when the application is loaded and use names to refer them?
In my application, we tried this, it worked. Also you can do some checking, e.g. the number of different possible values of a lookup in code should be the same as in db, if it's not, log/email/etc. But I don't want to manually code this for the status of 40+ biz entities.
Moreover, this can be part of the bigger problem of OR mapping. We're exposed with too much details of the persistence layer, and thus we have to take care of it. With technologies like Entity Framework, we don't need to worry about the "sync" part because it's automated, am I right?
Thanks!
我使用了与您所描述的类似的方法 - 数据库中的一个表，其中包含值和描述（对于报告等有用）以及代码中的枚举。我已经用代码中的注释处理了同步，内容是“这些值取自数据库 ABC 中的表 X”，以便程序员知道数据库需要更新。为了防止在没有相应代码更改的情况下从数据库端进行更改，我在表上设置了权限，以便只有某些人（希望他们记得他们也需要更改代码）才能访问。
I've used a similar method to what you're describing - a table in the database with values and descriptions (useful for reporting, etc.) and an enum in code. I've handled the synchronization with a comment in code saying something like "these values are taken from table X in database ABC" so that the programmer knows the database needs to be updated. To prevent changes from the database side without the corresponding changes in code I set permissions on the table so that only certain people (who hopefully remember they need to change the code as well) have access.
这些值必须是硬编码的，这实际上意味着它们不能在数据库中更改，这意味着将它们存储在数据库中是多余的。
因此，对它们进行硬编码，并且数据库中没有查找表。相反，将项目状态直接存储在项目表中。
The values have to be hard-coded, which effectively means that they can't be changed in the database, which means that storing them in the database is redundant.
Therefore, hard-code them and don't have a lookup table in the database. Instead store the items state directly in the items table.
您可以构建数据库，以便您的应用程序实际上不必关心代码本身，而是关心它们背后的业务规则。
我已完成以下两项操作：
IsAvailable？如果是这样，请将其作为标志列添加到代码表中，其中匹配的设置为true（或数据库的等效值），不匹配的设置为false< /代码>。EnvironmentSettings之类的单例表，其中包含诸如ItemStateIdOnReturn之类的列，该列是ItemState表的外键。如果我想避免在应用程序中声明枚举，我将使用#2 来解决问题中的示例。
是否采用此方法取决于应用程序的优先级。这种类型的结构是以额外的开发和查找开销为代价的。另外，如果每个单独的代码都有自己的业务规则，那么为每个所需的代码创建一个新列是不切实际的。
但是，如果您不想担心应用程序与代码表内容的同步，那么这可能是值得的。
You can structure your database so that your application doesn't actually have to care about the codes themselves, but rather the business rules behind them.
I have done both of the following:
IsAvailable, that the application cares about? If so, add it as a flag column to the code table, where those that match are set totrue(or your DB's equivalent), and those that don't are set tofalse.EnvironmentSettings, with a column such asItemStateIdOnReturnthat's a foreign key to theItemStatetable.If I wanted to avoid declaring an enum in the application, I would use #2 to address the example in the question.
Whether you take this approach depends on your application's priorities. This type of structure comes at the cost of additional development and lookup overhead. Plus, if every individual code comes with its own business rules, then it's not practical to create one new column per required code.
But, it may be worthwhile if you don't want to worry about synchronizing your application with the contents of a code table.