在Access中执行多值字段，打破规范化规则。 [英] Do Multivalue fields in Access, break normalization rules.
问题描述
[z [在Access中执行多值字段，打破规范化规则。]]
大家好，
这是我的第一个主题拍手拍拍
当很多人认为Multivalue Field会破坏数据库规范化规则时，这是不正确的。
第一件事，到了解什么是Microsoft Access的MVF，
我们必须想象它，看看它。
当你设置新的多值字段时，你可以选择很多根据需要将值放入1个字段。
但它实际上并没有像A，B，C那样保存在表格中。例如。
我得到了微软在10分钟前所做的事情
他们让那张桌子上有隐藏的物品，我的意思是，当你选择一个字段中的多值，所有值保存在fieldname.value中，我们不能直接在该表上访问它。
此函数在某种程度上类似于Query中的唯一记录。
让'想象一下，你必须用ID A和B创建2条记录，但其余字段是相同的。
有MVF，你的桌子还有ID为A和B的2记录
但它们都隐藏了，只显示1条记录A，B作为一行。
另一个例子，
你有这样的表
ID名称否
1 AB 10
2 AB 10
如果使用MVF，屏幕上应该是这样的
ID姓名否
1,2 AB 10
但实际上，这样保存的数据
--------- ID姓名否
value1 1 AB 10
value2 2 AB 10
与第一个普通表相同。你可以毫无困难地查询它。甚至用它来连接查询中的表。只需在MVF名称中添加.value。
[z[Do Multivalue fields in Access, break normalization rules.]]
Hi everyone,
This is my first topic, clap clap clap
when a lot of people think that Multivalue Field will break database normalization rule, that''s not correct.
first thing, to understand what is MVFs of Microsoft Access,
we have to imagine it, to see it.
when you set up new multivalue field, you can choose many value into 1 field as you want.
But it does not actually save in table like "A, B, C" for example.
I have got what Microsoft did about 10 mins ago
they make that table have hidden item, I mean, when you choose multi value in a field, all value save in the fieldname.value that we can''t directly access to it on that table.
this function somehow similar to Unique record in Query.
let''s imagine, You have to create 2 record with ID A and B, but the rest field are the same.
with MVFs, your table still have 2 record with ID A and B
but they all hidden, and only show 1 record "A, B" as a row.
another example,
you have table like this
ID Name No
1 AB 10
2 AB 10
if you use MVFs, it should be like this on screen
ID Name No
1,2 AB 10
but actually, data saved like this
---------ID Name No
value1 1 AB 10
value2 2 AB 10
the same as first normal table. And you can query it without any difficult. Even use it to join between table in query. just add .value in name of MVFs.
推荐答案
你是正确的，多值字段本身并没有打破背后的规范化概念场景实现。
它们确实打破了规范化的意图，沿着与表级别的查找字段相同的行（在此处读取表格中查找字段的邪恶）因为它们隐藏了实际的关系过程用户和其他任何允许（恕我直言）非常草率和难以维护数据库设计的人 - 并通过推断1NF规则。
You are correct in that multivalued fields in and of themselves do not break the normalization concept in their behind the scenes implementation.
They do break the intent of normalization, along the same lines that that lookup fields at the table level do (read here The Evils of Lookup Fields in Tables) in that they hide the actual relational process from the user and anyone else that follows allowing for (IMHO) very sloppy and hard to maintain database design - and by inference 1NF rules.
*第一范式（1NF）。第一种形式要求不存在多值项或重复组。所有字段必须是原子的，这意味着数据是可能的最小单位，不能进一步分割。
* First Normal Form (1NF). The first form requires that there be no multivalued items or repeating groups. All fields must be atomic, which means that the data is the smallest unit possible and can''t be further divided.
后台实现的后面强制1NF并没有改变数据库不是这样的事实在设计上正确规范化。
MVF不必要地使VBA /宏编码变得复杂
MVF不必要地复杂化SQL /查询结构
MVF阻止验证结点表，因为它们现在是隐藏的系统表。
MVF，afaik，在任何其他RDBMS中都不可用;因此，升级到MySQL，MariaDB，SQLServer，Oracle等等......不太可能发生。
恕我直言，只有一个原因在表级别使用MVF或LUF，这是用于共享点/访问应用程序。
That the behind the backstage implementation forces the 1NF doesn''t change the fact that the database is not properly normalized at design.
MVF needlessly complicate the VBA/Macro coding
MVF needlessly complicate SQL/Query structure
MVF prevent validation of the junction tables as they are now hidden system tables.
MVF , afaik, are not available in any other RDBMS; thus, upgrading to something like MySQL, MariaDB, SQLServer, Oracle, etc... isn''t likely to happen.
IMHO there is one and only one reason to ever use MVF or LUF at the table level and that is for share-point/access applications.
我喜欢这个MVF因为
first ，它让我从下拉列表（组合框）中勾选多值并存储为一组操作（我通常在操作中有很多项目）。
秒，我可以管理那些操作大图，我没有看到他们每一小块都要检查。
三，我没有任何使用MVF因为我使用1个简单的查询并从包含MVF的表中调用所有字段（如SELECT a AS ABC，b.value AS CDE FROM table1）并将其视为没有MVF的普通表。
fouth，与第一个相同，我可以从组合框中的droplist中选择多值，并避免使用Listbox，因为listbox多值需要另外要处理的VBA代码，列表框区域的信息和大小不像组合框的下拉列表那样灵活
I like this MVF because
first, it let me tick multivalue from dropdown list (combobox) and store as a group of operation ( I usually have a lot of item inside an operation).
second, I can manager those operation in big picture, I don''t have to see each small piece of them to check.
Third, I don''t have anytrouble using MVF coz I use 1 simple query and call all field from table containing MVF (like "SELECT a AS ABC, b.value AS CDE FROM table1") and treat it like a normal table without MVF.
fouth, same as first, I can choose multi value from droplist in combobox and avoid using Listbox since listbox multivalue need addition VBA code to process, and listbox area''s info and size are not as flexible as droplist of combobox
我喜欢这个MVF因为
I like this MVF because
hvsummer，我不是说MVF没有什么可比的;但是，这不是你文章的标题。
MVF（又名表级查找字段）违反了标准化的基本原则，原因在我之前的帖子中引用（同上链接）。 />
+随着数据库的增长，你会发现这个功能可能变得非常难以管理，并且有性能命中。
+使用允许多个值的查找字段无法强制执行数据完整性选择的选项（在查找向导中尝试它，它不会让你）这个风险孤立的记录在数据集中。
+唯一的好使用多值字段的原因是，如果您正在使用SharePoint Services，因为这是在Web界面中建立表关系的唯一方法
+请记住升级到一个成熟的RDMS，如SQL-Server，MySQL，MariaDB，Oracle可以很好地将多值字段转换为备注字段，因此在做出选择时请记住升级
其他要点：
hvsummer, I''m not saying that there''s not something to like about the MVF; however, that is not the title of your article.
MVF (aka table level lookup-fields) violate the very basic principle of normalization for the reasons cited in my prior post (ibid link).
+ As your database grows, your going to find that this "feature" can become very difficult to manage, and there are performance hits.
+ You cannot enforce data integrity using lookup fields with the "allow multiple values" option selected (try it in the lookup wizard, it wont let you) this risks orphaned records in the data set.
+ The only "good" reason to ever use multivalue fields is if you''re using SharePoint Services as this is the only means available to establish table relationships in the web-interface
+ Keep in mind that upgrading to a mature RDMS such as SQL-Server, MySQL, MariaDB, Oracle may very well convert a multivalue field to a Memo field, so keep upgrading in mind when making your choice
To address your other points:
首先，它让我从下拉列表（组合框）中勾选多值并存储为一组操作（我通常在操作中有很多项目）。
first, it let me tick multivalue from dropdown list (combobox) and store as a group of operation ( I usually have a lot of item inside an operation).
fouth，与第一个相同，我可以从组合框中的droplist中选择多个值并避免使用Listbox，因为listbox multivalue需要添加VBA代码来处理，并且列表框区域的信息和大小不像组合框的下拉列表那样灵活
fouth, same as first, I can choose multi value from droplist in combobox and avoid using Listbox since listbox multivalue need addition VBA code to process, and listbox area''s info and size are not as flexible as droplist of combobox
：-D
你真的有这里有一点：您不希望对正在尝试的操作使用Access-Macro或VBA代码。或者，您不希望在M：M关系的两个相关表之间创建连接表。
因为我们不知道您的应用程序的性质，所以无法帮助您找到可能避免您遇到的问题的解决方案，而不是在洞察力的范围内文章。
顺便说一下，VBA编码并不困难，而且经常会有SQL方法来处理应用程序需求。
:-D
You have really one point here: You do not want to have to use either Access-Macro or VBA code for the operations you are attempting. Or, you do not want to create the join table between the two related tables for a M:M relationship.
Because we don''t know the nature of your applications, it''s impossible to help you find the solutions that might avoid the issues you''re experiencing, not something that is within the scope of an "insights" article.
By the way, VBA coding is not that difficult and quite often there are SQL methods that will handle the application needs.
秒，我可以在大局中管理这些操作，我不必看到它们中的每一小块都要检查。
second, I can manager those operation in big picture, I don''t have to see each small piece of them to check.
这听起来很像前一点。
如果你在谈论查看相关表之间的相关记录，那么Form-Subform构造是你的朋友，并且通常与任何list / combo-box一样灵活，如果不是更灵活的话。再一次，可能需要一些VBA（例如这里＃17 ）取决于你的尝试;然而，我发现通常只是为了查看记录而不需要vba。
在一个新的主题中，如果你可以解释一下你正在尝试什么，那么也许我们可以提供你有更好的选择而不是查找字段。
This sounds an awful lot like the prior point(s).
If you are talking about viewing related records between related tables, then the Form-Subform construct is your friend and is quite often just as flexible, if not more so, than any list/combo-box. Once again, there may be a need for some VBA (such as here #17 ) depending on what you are attempting to do; however, I find that often just to view records no vba is required.
In a new thread, if you can explain a little more about what you are attempting, then maybe we can provide you with better alternatives than the lookup field.
第三，我没有任何使用MVF因为我使用1个简单查询并调用所有字段从包含MVF的表（如SELECT a AS ABC，b.value AS CDE FROM table1）中将其视为没有MVF的普通表。
Third, I don''t have anytrouble using MVF coz I use 1 simple query and call all field from table containing MVF (like "SELECT a AS ABC, b.value AS CDE FROM table1") and treat it like a normal table without MVF.
让我们从微软这里拿文章
具体两点：
了解绑定值和查找字段中的显示值
了解查找字段的显示值与绑定值之间的区别非常重要。默认情况下，显示值自动显示在数据表视图中。但是，绑定值是存储的内容，在查询条件中使用的内容，以及Access在与其他表的连接中默认使用的内容。
It''s important to understand the distinction between a lookup field''s display value and the bound value. The display value is automatically shown in datasheet view by default. However, the bound value is what is stored, what you use in query criteria, and what Access uses by default in joins with other tables.
你没有在带有MVF的表中看到实际值" stored" ，你会看到用户友好和用户友好。数据。具有主键和关联数据的表被隐藏...这将使任何不理解MVF（阅读）的人更难以编写复杂查询
You do not see the actual value "stored" in the table with the MVF, you see the "user friendly" data. The table that has the primary-key and the associated data is hidden... this will make writing complex queries much more difficult for anyone that doesn''t understand MVF (read on)
Searching for more than one value in a multivalued field
有时，您需要在多个值上搜索匹配项。假设您想要查看Kelly Rollin和Kelly Rollin这两个问题。和Lisa Miller是AssignedTo字段中的值之一。 Kelly Rollin的绑定值为6，Lisa Miller的绑定值为10.要为多值字段指定多个条件，可以使用AND和OR运算符。
Sometimes, you need to search for a match on more than one value. Suppose you want to see those issues in which both "Kelly Rollin" and "Lisa Miller" are among the values in the AssignedTo field. The bound value for Kelly Rollin is 6, and the bound value for Lisa Miller is 10. To specify several criteria for a multivalued field, you can use the AND and the OR operators.
现在Kelly Rollin。和Lisa Miller在你要搜索的表中显示如下：
[Issues_Title] [Issues_Assigned_To]
[问题2] [Kathleen Gail Jensen，Kelly Rollin，...... 。]
但是，SQL写成：
Now "Kelly Rollin" and "Lisa Miller" are shown in the table you want to search on as this:
[Issues_Title][Issues_Assigned_To]
[Issue 2 ][Kathleen Gail Jensen, Kelly Rollin,....]
However, the SQL is written as:
这篇关于在Access中执行多值字段，打破规范化规则。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
