查找字段的替代方法 [英] Alternatives to Lookup Fields
问题描述
嗨：
我对表访问和查找有一般性问题。
简化过程，并使用非常理论化的东西例如，假设我有两个表;
客户端和账单。
客户端表具有唯一的自动编号（已编制索引）和客户端名称，以及
作为地址和其他关键数据
我希望在Bill Table中记录一个密钥，将其链接到<中的唯一记录br />
客户名称表，以便我可以在报表上使用该记录中的数据以及账单数据
表数据。
一个复杂的问题是，一些客户会随着时间的推移改变他们的名字，而且我可以确定，从一天到下一天，某个客户将具有相同名称的
。他们
也经常移动（比法律提前一步？），所以除了原始的自动编号字段外，客户记录中的任何内容都不是一致的。
我的方法是在Bill表中放置一个查找字段，查找客户端
名称，但存储客户端密钥（表自动编号），这样无论它是什么，我都可以一直访问
这个名字。我知道这不是推荐的方法，而且我应该（根据神的数据库）使用客户端名称本身作为密钥
但是当客户端更改名称时出现问题，因为如果我在Bill表中使用了该名称，则在客户端表中更改客户端将会破坏链接..
我会很感激对这个悖论的一些评论，以及有关它的方法的建议
使得使用查找方法使系统更有效和更容易维护。
Best
John Baker
" John Baker" <巴****** @ Verizon.net>在消息中写道
新闻：电视******************************** @ 4ax.com ...嗨：
我有关于表格访问和查找的一般性问题。
过度简化，并使用非常理论化的东西例如，假设我有两个表;
客户端和比尔。
客户端表有一个唯一的自动编号（已编入索引）和客户端名称，以及
作为地址和其他关键数据
我希望在Bill Table中记录一个密钥，将其链接到
客户端中的唯一记录
名称表，以便我可以使用比尔表数据在报告中使用该记录中的数据。
一个复杂的问题是一些客户改变了他们的名字随着时间的推移，
我无法确定从一天到下一天某个客户将拥有相同的名称。他们还经常移动（比法律领先一步？），所以除了原始的自动编号字段外，客户端
记录中没有任何内容是一致的。
我的方法一直是在Bill表中放置一个查找字段，查看客户端名称，但是存储客户端密钥（表自动编号），以便我可以一直访问
这个名字无论它是什么。我知道这不是推荐的方法，而且我应该（根据神的数据库）使用客户端名称本身
作为密钥
[snip]
你完全误解了数据库之神。存储一个密钥和
显示名称是完全按照正确的设计应该做什么
原则。但是，这不需要使用查找字段。你
只需在FORM上使用一个ComboBox来做同样的事情。
基本的问题使用查找字段时，它们提供了一个界面，当查看表格时，表格更适合提供。由于除了开发人员在调试期间不会直接查看
表，因此没有理由（在表格中）存储一个东西，同时显示另一个。
-
我没有查看此邮件附带的电子邮件帐户
。发送给... ...
在Hunter dot com的RBrandt
Rick：
是的你的对，我确实误解了。
关键是，我应该在表格上使用一个组合框，然后设置Como Box
来保存账单记录中客户名称的自动编号键。
对吗？
最好还是谢谢
John
" Rick Brandt" < RI ********* @ hotmail.com>写道：
" John Baker" <巴****** @ Verizon.net>在消息中写道
新闻：电视******************************** @ 4ax.com .. < blockquote class =post_quotes>嗨：
我有一个关于表格访问和查找的一般问题。
过度简化的事情，并使用一个非常理论的例子，让我们说我有两张桌子;
客户和比尔。
客户表有一个唯一的自动编号（已编入索引）和客户名称，以及<地址和其他关键数据
我希望在Bill Table中记录一个密钥，将其链接到
Client Name Table中的唯一记录
我可以使用比尔表数据在报告中使用该记录中的数据。
一个复杂因素是一些客户随着时间的推移改变了他们的名字，<并且我不能确定从一天到下一天，给定的客户将具有相同的名称。他们还经常移动（比法律领先一步？），所以除了原始的自动编号字段外，客户端
记录中没有任何内容是一致的。
我的方法一直是在Bill表中放置一个查找字段，查看客户端名称，但是存储客户端密钥（表自动编号），以便我可以一直访问
这个名字无论它是什么。我知道这不是推荐的方法，而且我应该（根据神的数据库）使用客户端名称本身
作为密钥
[snip]
你完全误解了数据库之神。根据正确的设计原则，存储密钥并显示名称是完全应该做的。但是，这不需要使用查找字段。你只需在FORM上使用一个ComboBox来做同样的事情。
基本的问题。查找字段是指在查看表格时应该更适合提供的表格时提供界面。由于开发人员在调试过程中不应直接查看表格，因此没有理由（在表格中）存储一件事物，同时显示另一件事。
" John Baker" <巴****** @ Verizon.net>在消息中写道
news：4p ******************************** @ 4ax.com ...Rick：
是的，你的权利让我误解了。
关键是，我应该使用一个组合框表单，然后
设置Como Box以在Bill记录中保存客户名称的Autonumber密钥。
对吗？
确切地说。
只是为了添加...你的原始帖子似乎也偏离了自动编号
对比自然密钥辩论真的是一个不同的主题。您可以使用存储密钥的策略，同时使用自然密钥或代理密钥（如自动编号）显示其他内容。
。虽然辩论在这一点上主要是宗教性的，但我同意，当最好的
自然候选人可能会被更改或需要时
多个字段生成一个我倾向于选择的自然键
代理。
当然，代理键也不需要是一个自动编号。
帐号，订单号，发票编号等等都是代理商
的时尚。
-
我没有查看此邮件附带的电子邮件帐户
。发送给...
在Hunter dot com的RBrandt
Hi:
I have a general question about table access and look ups.
Over simplifying things,and using a very theoretical example, lets say I have two tables;
Client and Bill.
The client table has a unique auto number (which is indexed) and the Client name, as well
as address and other key data
I wish to record a key in the Bill Table that will link it to a unique record in the
Client Name Table, so that I can use data in that record on a report along with the Bill
Table data.
A complication is that some of the some clients change their names over time, and I can
not be certain from one day to the next that a given client will have the same name. They
also move frequently (one step ahead of the law?), so nothing in the Client Record is
consistent except the original auto number field.
My approach has been to put a lookup field in the Bill table, looking up on the client
name, but store the Client Key (the table auto number), so that I can consistently access
the name regardless of what it is. I know that this is not the recommended approach, and
that I should (according to the Gods of Databases) use the client name itself as the key
BUT that presents a problem when the client changes there name, since changing the client
name in the client table will destroy the link if I have used the name in the Bill Table..
I would appreciate some comment on this paradox, and suggestions for ways around it that
make the system more effective and easier to maintain that using a lookup approach.
Best
John Baker
"John Baker" <Ba******@Verizon.net> wrote in message
news:tv********************************@4ax.com...Hi:
I have a general question about table access and look ups.
Over simplifying things,and using a very theoretical example, lets say I have
two tables;
Client and Bill.
The client table has a unique auto number (which is indexed) and the Client
name, as well
as address and other key data
I wish to record a key in the Bill Table that will link it to a unique record
in the
Client Name Table, so that I can use data in that record on a report along
with the Bill
Table data.
A complication is that some of the some clients change their names over time,
and I can
not be certain from one day to the next that a given client will have the same
name. They
also move frequently (one step ahead of the law?), so nothing in the Client
Record is
consistent except the original auto number field.
My approach has been to put a lookup field in the Bill table, looking up on
the client
name, but store the Client Key (the table auto number), so that I can
consistently access
the name regardless of what it is. I know that this is not the recommended
approach, and
that I should (according to the Gods of Databases) use the client name itself
as the key
[snip]
You''ve completely misunderstood the "Gods of Databases". Storing a key and
displaying the name is EXACTLY what you should do according to proper design
principles. This does NOT however require the use of a lookup field. You
simply use a ComboBox on a FORM to do the same exact thing.
The basic "issue" with lookup fields is that they provide an interface when
viewing a table that should more appropriately be provided by a form. Since
tables should never be viewed directly except by the developer during debugging
there is no reason to (in a table) to store one thing while displaying another.
--
I don''t check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Rick:
Yes your right I did misunderstand.
The point, then, is that I should use a Combo Box on the form, and then set the Como Box
to save the Autonumber key for the Client name in the Bill record.
Right?
Best and thanks
John
"Rick Brandt" <ri*********@hotmail.com> wrote:
"John Baker" <Ba******@Verizon.net> wrote in message
news:tv********************************@4ax.com.. .Hi:
I have a general question about table access and look ups.
Over simplifying things,and using a very theoretical example, lets say I have
two tables;
Client and Bill.
The client table has a unique auto number (which is indexed) and the Client
name, as well
as address and other key data
I wish to record a key in the Bill Table that will link it to a unique record
in the
Client Name Table, so that I can use data in that record on a report along
with the Bill
Table data.
A complication is that some of the some clients change their names over time,
and I can
not be certain from one day to the next that a given client will have the same
name. They
also move frequently (one step ahead of the law?), so nothing in the Client
Record is
consistent except the original auto number field.
My approach has been to put a lookup field in the Bill table, looking up on
the client
name, but store the Client Key (the table auto number), so that I can
consistently access
the name regardless of what it is. I know that this is not the recommended
approach, and
that I should (according to the Gods of Databases) use the client name itself
as the key
[snip]
You''ve completely misunderstood the "Gods of Databases". Storing a key and
displaying the name is EXACTLY what you should do according to proper design
principles. This does NOT however require the use of a lookup field. You
simply use a ComboBox on a FORM to do the same exact thing.
The basic "issue" with lookup fields is that they provide an interface when
viewing a table that should more appropriately be provided by a form. Since
tables should never be viewed directly except by the developer during debugging
there is no reason to (in a table) to store one thing while displaying another.
"John Baker" <Ba******@Verizon.net> wrote in message
news:4p********************************@4ax.com...Rick:
Yes your right I did misunderstand.
The point, then, is that I should use a Combo Box on the form, and then set the Como Box to save the Autonumber key for the Client name in the Bill record.
Right?
Exactly.
Just to add... your original post also seemed to stray into the "AutoNumber
versus Natural Key" debate which is really a different subject. You can
use the strategy of storing the key while displaying something else when
using either a Natural Key or a Surrogate Key (like AutoNumber). While
that debate is largely religious at this point, I agree that when the best
natural key candidates are subject to being changed or when it requires
multiple fields to generate a Natural Key that I tend to prefer a
Surrogate.
Of course, it is also true that a Surrogate Key need not be an AutoNumber.
Account Numbers, Order Numbers, Invoice Numbers, etc., are all surrogates
of a fashion.
--
I don''t check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
这篇关于查找字段的替代方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
