针对查找值进行设计
我想知道您的开发商店或项目中用于处理查找值的协议是什么，例如世界各国或美利坚合众国的各州。
我看到它以两种不同的方式完成：在我工作的一个地方，我们的查找全部存储在带有前缀“L_”的数据库表中，并具有以下列：id、code、desc、ordersequence。因此，例如，对于世界各国，我们会有一个如下表：
CREATE TABLE L_Countries(
CountryID INT IDENTITY(1,1) PRIMARY KEY,
CountryCode VARCHAR(10) NOT NULL,
CountryDesc VARCHAR(50) NOT NULL,
OrderSequence INT NULL
)
最近我看到一个示例，其中查找被烘焙到枚举中，例如：
enum Countries
{
Croatia = 1,
Slovenia = 2,
Serbia = 3,
// and so on
}
如果这些源自数据库表，则有一个实用程序这将为枚举生成 C# 代码。否则，有人会花时间对所有条目进行硬编码，并假设这些值不太可能改变。对于枚举中的数值，它们根据数据库中相应查找表中的 ID 列（如果存在）进行硬编码，或者仅根据从 1 开始的条目进行硬编码。
不可否认，我赞成前一种方法的论点，是它非常灵活，可以选择使用代码或完整描述、操作顺序以及进行更改而无需重新编译。尽管可以选择从它们生成代码，但一个很大的优点是，当将它们降级到数据库时，它们仍然是真正的“查找”。最后，它们的使用方式很灵活：作为 Dictionary 集合中的值或使用服务器端代码生成 ASP.NET ListItems 或 html 组合框元素。
我听到的关于后者的论点是值不会改变，并且即使它们在应用程序级别缓存，也必须从数据库检索查找值会产生开销。通过对它们进行硬编码或生成枚举，它们可以不受数据库检索的影响而可用。
我的问题如下：
哪个选项对您来说更有意义，或者，如果您的答案是“视情况而定”，您能否给出一个场景，其中硬编码枚举比整个应用程序中的数据库查找更好？
您是否见过其他处理查找的优雅解决方案？我曾经开发过一个应用程序，其中有一个用于所有查找的表（它包括“lookupname”列）作为示例。您的替代方法与上述两种方法相比如何？
I am wondering what the protocol is in your development shop or project for dealing with lookup values, such as countries of the world or states in the United States of America.
I have seen it done in two different ways: at one place I worked our lookups were all stored in a database table with the prefix "L_" and had the following columns: id, code, desc, ordersequence. So, for example, for countries of the world we would have a table like:
CREATE TABLE L_Countries(
CountryID INT IDENTITY(1,1) PRIMARY KEY,
CountryCode VARCHAR(10) NOT NULL,
CountryDesc VARCHAR(50) NOT NULL,
OrderSequence INT NULL
)
More recently I have seen an example where lookups are baked into Enumerations, for example:
enum Countries
{
Croatia = 1,
Slovenia = 2,
Serbia = 3,
// and so on
}
In the event that these are originating from a database table, there is a utility that will generate the C# code for the enumeration. Otherwise someone just takes the time to hardcode all the entries using the assumption that the values are not likely to change. For the numeric values in the enumeration, they are hardcoded based on the ID column in the corresponding lookup table in the database if it exists, or just hardcoded based on entry starting from 1.
The argument for the former approach which, admittedly, I favor, is that it is quite flexible giving the option for using codes or full descriptions, manipulating order, and making changes without having to recompile. Although the option to generate code from them is possible, a big advantage is that they remain true "lookups" when relegated to the database. Finally, the way they can be used is flexible: as values in a Dictionary collection or generating ASP.NET ListItems or html combobox elements with server side code.
Arguments I have heard for the latter is that values will not change, and that there is overhead in having to retrieve lookup values from the database even if they are cached at the application level. By hardcoding them or generating an Enum, they are available without the penalty of database retrieval.
My questions are as follows:
Which option makes more sense to you or, if your answer is "it depends," can you give a scenario where the hardcoded enumerations are better than a database lookup across an entire application?
Are there other ways that you have seen which are an elegant solution to dealing with lookups? I have worked on an application where there was a single table for all the lookups (it included a "lookupname" column) as an example. How does your alternate approach compare to the above two approaches?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果信息非常静态并用于流量控制等。我会使用枚举（即我们将它们用于医疗表格类型、医疗表格上的各种选项等）
如果信息是静态的但较大，或者没有值或者通过将其设为枚举来提供表达力，并且连接等不需要它。我们将其存储在 XML 或哈希表中，并根据需要读取它和/或嵌入资源。
如果信息将在数据库连接中使用，信息太大，或者如果将其存储在数据库查找表中更实用，我们通常会使用 int 作为主键。
希望有帮助
If the information is very static and used for flow control, etc. I would use an enum (i.e. we use them for medical form types, various options on medical forms, etc.)
If the information is static but larger, or if no value or expressiveness would be offered by making it an enum, and it is not needed for joins, etc. we store it in XML or a hashtable and read it in as needed and/or embed the resource.
If the information is going to be used in database joins, is excessively large, or if it is simply more practical to store it in a database lookup table, we do so, generally with an int as a primary key.
Hope that helps
这些方法并不相互排斥。我使用 CodeDom 从数据库表自动生成枚举代码。存在一个引导问题（即如何填充和管理数据库本身），但您可以在构建步骤中生成枚举，从而确保两者永远不会不同步。
These approaches are not mutually exclusive. I used CodeDom to autogenerate the enum code from the database table. There is a bootstrap issue (i.e. how do you populate and manage the database itself), but you could generate the enum in a build step, thereby ensuring that the two never get out of sync.
国家/地区代码的 ISO 标准为 2 或 3 个字符。国家/地区名称的 ISO 标准为 40 个字符。 ISO 还指定了所有国家/地区的数字标识符。
我认为使用 IDENTITY 作为此表中的键没有意义，因为这些值显然需要在设计时为 ENUM 生成，而不是在运行时插入。除非以某种方式使用它，否则我建议您删除 IDENTITY 属性并使国家/地区代码唯一。就目前情况而言，从数据完整性的角度来看，该设计很薄弱，因为它缺乏自然密钥。
The ISO standard for country codes is either 2 or 3 characters. The ISO standard for country names is 40 characters. ISO also specifies numeric identifiers for all countries.
I see no point in using IDENTITY as a key in this table because the values apparently need to be generated at design time for the ENUM and not inserted at runtime. Unless it is being used somehow then I suggest you drop the IDENTITY property and also make the country code unique. As it stands the design is weak from a data integrity perspective because it lacks a natural key.