查找表有多重要？ [英] How important are lookup tables?

问题描述

我写的许多应用程序使用查找表，因为这只是我被教导的方式（标准化等等）。问题是我所做的查询往往比较复杂，因为这样。他们经常看起来像这样

获取所有仍然打开的帖子

 SELECT * FROM posts WHERE status_id =（SELECT id FROM statuses WHERE name ='open'）
  

通常情况下，查找表本身很短。例如，可能只有3个左右的不同状态。在这种情况下，是否可以在应用程序中使用常量来搜索某种类型？

获取所有仍然打开的帖子

 SELECT * FROM posts WHERE status_id =.Status :: OPEN 
  

或者，如果不是使用外部id，我将其设置为枚举并查询？

谢谢。

解决方案

如果您仅限于MyNonSQL中的小型文件系统，或者您正在考虑SQL和大型数据库，那么答案有所回避。

在真正的数据库中，有许多应用程序使用一个数据库，许多使用不同报表工具（不仅仅是应用程序）的用户访问数据，标准，规范化和开放架构要求很重要。

尽管人们试图改变归一化等的定义，以适应目的，但归一化没有改变。

• 如果您在数据表中重复打开和已关闭，这是一个简单的规范化错误。如果您更改这些值，您可能必须更新数百万行，这是非常有限的设计。这些值通常被归一化为参考或查找表。它也节省空间。 开放，已关闭等值不再重复。




• 第二点是易于更改，如果已关闭更改为已过期，再一次，需要改变一行，这反映在整个数据库中;而在未正规化的文件中，需要更改数百万行。




• 添加新值只是插入一行。

  在开放式架构术语中，查找表是一个普通的表格，在
  

• 它存在于（标准SQL）目录中;任何报表工具都可以找到它，只要定义了PK :: FK关系，报表工具也可以找到。




• 枚举仅适用于非SQLS。在SQL中，枚举是一个查找表。




• 下一个关键点与键的意义相关。如果密钥对用户无意义，则可以使用INT或TINYINT或任何适合的密钥;递增编号允许差距。但是如果Key对用户有意义，不要使用无意义的数字，请使用有意义的键。男性和女性的M和F等。

  
  
  
  
  
  • 现在有些人会进入切线，以保持PK的持久性。这是一个单独的点。是的，当然，总是使用稳定的PK值。 M和F不太可能改变;如果你使用{0,1,2,4,5,6}，那么不要改变它，你为什么要这样做。这些价值观应该是无意义的，只有有意义的关键需要改变。
    
    。
  
  



• 如果您使用有意义的键，使用短字母代码，用户和开发人员都可以轻松了解（并推断




• 由于PK稳定，特别是在Lookup表中，您可以安全地编码：

  
  
  

  WHERE status_id ='O'

  
  
  

  您不必与查找表一起查看价值开。这会失去代码段中的查找表的价值。

SQL是一种麻烦的语言，特别是当它来加入。但是这就是我们所有的，所以我们只需要接受产权负担并处理它。你的示例代码很好。但更简单的形式可以做同样的事情。报表工具将产生：

 SELECT p。*，
 s.name 
 FROM posts p，
 status s 
 WHERE p .status_id = s.status_id 
 AND p.status_id ='O' 

• 对于银行系统，我们使用有意义的短代码（因为它们是有意义的，我们不会随季节改变），给定一个查找表，如（仔细选择） ，类似于ISO国家代码）：

   
   Eq Equity 
   EqCS权益/普通股
   O在柜台
   OF OTC /未来 

  
  
  

  这样的代码很常见：

  
  
  

  WHERE InstrumentTypeCode LIKEEq％

用户将从显示打开，关闭等的下拉列表中选择不是{0,1,2,4,5,6}而不是{M，F，U}的值。在应用程序和报表工具中。没有查找表，你不能这样做。

最后，如果数据库很大，并且支持BI或DSS或OLAP功能（高度归一化的数据库） ，那么查找表实际上是维度或向量，在维度事实分析中。如果不存在，则必须添加，才能满足该软件的要求，然后才能安装此类分析。

A lot of the applications I write make use of lookup tables, since that was just the way I was taught (normalization and such). The problem is that the queries I make are often more complicated because of this. They often look like this

get all posts that are still open

"SELECT * FROM posts WHERE status_id = (SELECT id FROM statuses WHERE name = 'open')"

Often times, the lookup tables themselves are very short. For instance, there may only be 3 or so different statuses. In this case, would it be okay to search for a certain type by using a constant or so in the application? Something like

get all posts that are still open

"SELECT * FROM posts WHERE status_id = ".Status::OPEN

Or, what if instead of using a foreign id, I set it as an enum and queried off of that?

Thanks.

解决方案

The answer depends a little if you are limited to small filing systems in MyNonSQL, or if you are thinking about SQL and large databases.

In real Databases, where there are many apps using one database, and many users using different report tools (not just the apps) to access the data, standards, normalisation, and open architecture requirements are important.

Despite the people who attempt to change the definition of "normalisation", etc. to suit the purpose, Normalisation has not changed.

• if you have "Open" and "Closed" repeated in data tables, that is a simple Normalisation error. If you change those values you may have to update millions of rows, which is very limited design. Such values are commonly normalised into a Reference or Lookup table. It also saves space. The value "Open", "Closed" etc is no longer duplicated.

• the second point is ease of change, if "Closed" were changed to "Expired", again, one row needs to be changed, and that is reflected in the entire database; whereas in the unnormalised files, millions of rows need to be changed.

• Adding new values is simply a matter of inserting one row.

• in Open Architecture terms, the Lookup table is an ordinary table. It exists in the (standard SQL) catalogue; any report tool can find it, as long as the PK::FK relation is defined, the report tool can find that as well.

• Enum is only for the Non-SQLS. In SQL the Enum is a Lookup table.

• The next point relates to the meaningfulness of the key. If the Key is meaningless to the user, fine, use an INT or TINYINT or whatever is suitable; number them incrementally; allow "gaps". But if the Key is meaningful to the user, do not use a meaningless number, do use the meaningful key. "M" and "F" for Male and Female, etc.

  • Now some people will get in to tangents re the permanence of PKs. That is a separate point. Yes, of course, always use a stable value for a PK. "M" and "F" are unlikely to change; if you have used {0,1,2,4,5,6}, well don't change it, why would you want to. Those values were supposed to be meaningless, only meaningful Key need to be changed.
    .

• if you do use meaningful keys, use short alphabetic codes, that both users and developers can readily understand (and infer to long description from).

• Since PKs are stable, particularly in Lookup tables, you can safely code:

  WHERE status_id = 'O'

  You do not have to join with the Lookup table and examine the Value "Open". That loses the value of the Lookup table in the code segments.

SQL is a cumbersome language, especially when it comes to joins. But that is all we have, so we need to just accept the encumbrance and deal with it. Your example code is fine. But simpler forms can do the same thing. A report tool would generate:

SELECT  p.*,
         s.name
    FROM posts p, 
         status s
    WHERE p.status_id = s.status_id 
    AND   p.status_id = 'O'

• For banking systems, where we use short codes which are meaningful (since they are meaningful, we do not change them with the seasons, we just add to them), given a Lookup table such as (carefully chosen, similar to ISO Country Codes):

  Eq   Equity
  EqCS Equity/Common Share
  O    Over The Counter
  OF   OTC/Future

  Code such as this is common:

  WHERE InstrumentTypeCode LIKE "Eq%"

And the users would choose the value from a drop-down that displayed "Open", "Closed", etc., not {0,1,2,4,5,6}, not {M, F, U}. Both in the apps, and in the report tool. Without a lookup table, you can't do that.

Last, If the database was large, and supported BI or DSS or OLAP functions (the highly Normalised databases do), then the Lookup table is actually a Dimension or Vector, in Dimension-Fact analyses. If it was not there, then it would have to be added in, to satisfy the requirements of that software, before such analyses can be mounted.

这篇关于查找表有多重要？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！