When I create a view I can base it on multiple columns from different tables. When I want to create a lookup table I need information from one table, for example the foreign key of an order table, to get customer details from another table. I can create a view having parameters to make sure it will get all data that I need. I could also - from what I have been reading - make a lookup table. What is the difference in this case and when should I choose for a lookup table?? I hope this ain't a bad question, I'm not very into db's yet ;).
当我创建一个视图时，我可以将它基于来自不同表的多个列。当我想创建一个查找表时，我需要来自一个表的信息，例如订单表的外键，以从另一个表中获取客户详细信息。我可以创建一个具有参数的视图，以确保它将获得我需要的所有数据。我也可以 - 从我一直在阅读的内容 - 制作一个查找表。在这种情况下有什么区别，我何时应该选择查找表？我希望这不是一个糟糕的问题，我不是很喜欢db的;）。
5 个解决方案
#1
2
I think your usage of "Lookup Table" is slightly awry. In normal parlance a lookup table is a code or reference data table. It might consist of a CODE and a DESCRIPTION or a code expansion. The purpose of such tables is to provide a lsit of permitted values for restricted columns, things like CUSTOMER_TYPE or PRIORITY_CODE. This category of table is often referred to as "standing data" because it changes very rarely if at all. The value of defining this data in Lookup tables is that they can be used in foreign keys and to populate Dropdowns and Lists Of Values.
我认为你对“查找表”的使用有点不对劲。按照通常的说法，查找表是代码或参考数据表。它可能包含CODE和描述或代码扩展。这些表的目的是为受限制的列提供lsit允许值，例如CUSTOMER_TYPE或PRIORITY_CODE。这类表通常被称为“常设数据”，因为它很少发生变化。在Lookup表中定义此数据的值是，它们可以在外键中使用，并填充Dropdown和Lists Of Values。
What you are describing is a slightly different scenario:
你所描述的是一个略有不同的场景：
I need information from one table, for example the foreign key of an order table, to get customer details from another table
我需要来自一个表的信息，例如订单表的外键，以从另一个表中获取客户详细信息
Both these tables are application data tables. Customer and Order records are dynamic. Now it is obviously valid to retrieve additional data from the Customer table to display along side the Order data, and in that sense Customer is a "lookup table". More pertinently it is the parent table of Order, because it has the primary key referenced by the foreign key on Order.
这两个表都是应用程序数据表。客户和订单记录是动态的。现在，从Customer表中检索附加数据以显示Order数据旁边显然是有效的，从这个意义上说，Customer是一个“查找表”。更有针对性的是它是Order的父表，因为它具有Order上的外键引用的主键。
By all means build a view to capture the joining logic between Order and Customer. Such views can be quite helpful when building an application that uses the same joined tables in several places.
通过所有方式构建一个视图来捕获Order和Customer之间的连接逻辑。在构建在多个位置使用相同连接表的应用程序时，此类视图非常有用。
#2
2
Creating a view gives you a "live" representation of the data as it is at the time of querying. This comes at the cost of higher load on the server, because it has to determine the values for every query. This can be expensive, depending on table sizes, database implementations and the complexity of the view definition.
创建视图会为您提供查询时的“实时”数据表示。这是以服务器上的更高负载为代价的，因为它必须确定每个查询的值。这可能很昂贵，具体取决于表大小，数据库实现和视图定义的复杂性。
A lookup table on the other hand is usually filled "manually", i. e. not every query against it will cause an expensive operation to fetch values from multiple tables. Instead your program has to take care of updating the lookup table should the underlying data change.
另一方面，查找表通常是“手动”填充，即。即并非每个针对它的查询都会导致昂贵的操作从多个表中获取值。相反，如果基础数据发生变化，您的程序必须负责更新查找表。
Usually lookup tables lend themselves to things that change seldomly, but are read often. Views on the other hand - while more expensive to execute - are more current.
通常查找表适合于很少改变但经常阅读的内容。另一方面，观点 - 虽然执行成本更高 - 更新。
#3
0
Read this tutorial and you may find helpful info when a lookup table is needed:
阅读本教程，您可以在需要查找表时找到有用的信息：
SQL：创建查找表
#4
0
Here's an example of a lookup table. We have a system that tracks Jurors, one of the tables is JurorStatus. This table contains all the valid StatusCodes for Jurors:
这是一个查找表的示例。我们有一个跟踪陪审员的系统，其中一个表是JurorStatus。此表包含陪审团的所有有效StatusCodes：
Code: Value
WS : Will Serve
PP : Postponed
EM : Excuse Military
IF : Ineligible Felon
代码：价值WS：将服务PP：推迟EM：借口军事IF：不合格的Felon
This is a lookup table for the valid codes.
这是有效代码的查找表。
A view is like a query.
视图就像一个查询。
#5
0
Just learn to write sql queries to get exactly what you need. No need to create a view! Views are not good to use in many instances, especially if you start to base them on other views, when they will kill performance. Do not use views just as a shorthand for query writing.
只需学习编写SQL查询即可获得所需内容。无需创建视图！在许多情况下使用视图并不好用，特别是如果你开始将它们放在其他视图上，那么它们会破坏性能。不要将视图用作查询编写的简写。
#1
2
I think your usage of "Lookup Table" is slightly awry. In normal parlance a lookup table is a code or reference data table. It might consist of a CODE and a DESCRIPTION or a code expansion. The purpose of such tables is to provide a lsit of permitted values for restricted columns, things like CUSTOMER_TYPE or PRIORITY_CODE. This category of table is often referred to as "standing data" because it changes very rarely if at all. The value of defining this data in Lookup tables is that they can be used in foreign keys and to populate Dropdowns and Lists Of Values.
我认为你对“查找表”的使用有点不对劲。按照通常的说法，查找表是代码或参考数据表。它可能包含CODE和描述或代码扩展。这些表的目的是为受限制的列提供lsit允许值，例如CUSTOMER_TYPE或PRIORITY_CODE。这类表通常被称为“常设数据”，因为它很少发生变化。在Lookup表中定义此数据的值是，它们可以在外键中使用，并填充Dropdown和Lists Of Values。
What you are describing is a slightly different scenario:
你所描述的是一个略有不同的场景：
I need information from one table, for example the foreign key of an order table, to get customer details from another table
我需要来自一个表的信息，例如订单表的外键，以从另一个表中获取客户详细信息
Both these tables are application data tables. Customer and Order records are dynamic. Now it is obviously valid to retrieve additional data from the Customer table to display along side the Order data, and in that sense Customer is a "lookup table". More pertinently it is the parent table of Order, because it has the primary key referenced by the foreign key on Order.
这两个表都是应用程序数据表。客户和订单记录是动态的。现在，从Customer表中检索附加数据以显示Order数据旁边显然是有效的，从这个意义上说，Customer是一个“查找表”。更有针对性的是它是Order的父表，因为它具有Order上的外键引用的主键。
By all means build a view to capture the joining logic between Order and Customer. Such views can be quite helpful when building an application that uses the same joined tables in several places.
通过所有方式构建一个视图来捕获Order和Customer之间的连接逻辑。在构建在多个位置使用相同连接表的应用程序时，此类视图非常有用。
#2
2
Creating a view gives you a "live" representation of the data as it is at the time of querying. This comes at the cost of higher load on the server, because it has to determine the values for every query. This can be expensive, depending on table sizes, database implementations and the complexity of the view definition.
创建视图会为您提供查询时的“实时”数据表示。这是以服务器上的更高负载为代价的，因为它必须确定每个查询的值。这可能很昂贵，具体取决于表大小，数据库实现和视图定义的复杂性。
A lookup table on the other hand is usually filled "manually", i. e. not every query against it will cause an expensive operation to fetch values from multiple tables. Instead your program has to take care of updating the lookup table should the underlying data change.
另一方面，查找表通常是“手动”填充，即。即并非每个针对它的查询都会导致昂贵的操作从多个表中获取值。相反，如果基础数据发生变化，您的程序必须负责更新查找表。
Usually lookup tables lend themselves to things that change seldomly, but are read often. Views on the other hand - while more expensive to execute - are more current.
通常查找表适合于很少改变但经常阅读的内容。另一方面，观点 - 虽然执行成本更高 - 更新。
#3
0
Read this tutorial and you may find helpful info when a lookup table is needed:
阅读本教程，您可以在需要查找表时找到有用的信息：
SQL：创建查找表
#4
0
Here's an example of a lookup table. We have a system that tracks Jurors, one of the tables is JurorStatus. This table contains all the valid StatusCodes for Jurors:
这是一个查找表的示例。我们有一个跟踪陪审员的系统，其中一个表是JurorStatus。此表包含陪审团的所有有效StatusCodes：
Code: Value
WS : Will Serve
PP : Postponed
EM : Excuse Military
IF : Ineligible Felon
代码：价值WS：将服务PP：推迟EM：借口军事IF：不合格的Felon
This is a lookup table for the valid codes.
这是有效代码的查找表。
A view is like a query.
视图就像一个查询。
#5
0
Just learn to write sql queries to get exactly what you need. No need to create a view! Views are not good to use in many instances, especially if you start to base them on other views, when they will kill performance. Do not use views just as a shorthand for query writing.
只需学习编写SQL查询即可获得所需内容。无需创建视图！在许多情况下使用视图并不好用，特别是如果你开始将它们放在其他视图上，那么它们会破坏性能。不要将视图用作查询编写的简写。