是否可以违反第一正常形式？ [英] Is it ever okay to violate first normal form?

问题描述

我有一个Access数据库，需要在一行上显示几个一对多关系的数据（例如，它会列出项目为a，b，e，f，我会有多列像那样）。我知道这是一个糟糕的想法存储数据，以及，但考虑到我允许用户过滤这些列，我不能想到一个更好的方式处理数据，违反第一正态形式。

I have an Access database that will be required to present data with several one-to-many relationships on one row (e.g., it would list items as "a, b, e, f", and I would have multiple columns like that). I know it's a bad idea to store data that way as well, but considering that I'm allowing the user to filter on several of these columns, I can't think of a better way of dealing with the data than violating first normal form.

例如：我有几篇杂志文章，每篇文章都可以报告多种动物和多种蔬菜。用户可以过滤源名称，或者他们可以过滤一个或多个动物和一个或多个蔬菜。输出应类似于

As an example: say that I have several journal articles, each of which may report on multiple animals and multiple vegetables. The user can filter on the source name, or they can filter on one or more animals and one or more vegetables. The output should look like

Source name....animals...............vegetables
Source 1.......dogs, cats, birds.....carrots, tomatoes
Source 2.......leopards, birds.......tomatoes, zucchini, apples
Source 3.......cats, goldfish........carrots, cucumbers

通常你会有一个单独的表与源名称+动物：

Typically you would have a separate table with Source name + animal:

Source name......animal
Source 1.........dog
Source 1.........cats
Source 1.........birds
Source 2.........leopards

etc

和一个类似的蔬菜表。但考虑如何将数据提交给用户（逗号分隔列表），以及用户如何过滤数据（他可能过滤只看到包括狗和猫的来源，以及胡萝卜和西红柿的来源），我认为将数据作为动物和蔬菜的逗号分隔列表存储是有意义的。用逗号分隔的列表，当用户选择多个蔬菜和多个动物时，我可以说

and a similar table for vegetables. But considering how the data needs to be presented to the user (a comma-separated list), and how the user filters the data (he may filter to only see sources that include dogs and cats, and sources with carrots and tomatoes), I think it makes sense to store the data as comma separated lists for animals and vegetables. With a comma-separated list, when the user selects multiple vegetables and multiple animals, I can say

WHERE (Vegetables like "*carrots*" and Vegetables like "*tomatoes*") AND (Animals like *dogs*" and Animals like "*cats*")

我不能想到一个有效的方法来做这个类似的查询在Access，而不使用大量的VBA和多个查询。

I can't think of an efficient way to do this same kind of query in Access without using a lot of VBA and multiple queries.

推荐答案

您可以随时构建一个违反任何规则的场景，因此标题中的问题的答案为是。

You can always construct a scenario in which violating any rule makes sense, so the answer to the question in your title is Yes.

然而，这不是那些场景之一。您提出的搜索和表示问题对于大多数一对多关系是常见的（或者至少是多个一对多关系），如果这是违反第一个正常表单的原因，那么您将看不到大量的规范化数据库。

This is not one of those scenarios, however. The searching and presentation issues you raise are common to most one-to-many relationships (or, at least, to many one-to-many relationships) and if this were a reason to violate first normal form then you wouldn't see a lot of normalized databases.

正确构造数据库，您不必担心逗号，搜索字词彼此嵌入，以及由于缺少索引而导致的缓慢搜索。编写一个可重复使用的代码片段，为您执行逗号分隔汇总，这样您就不必再重复发明。

Construct the database correctly and you won't have to worry about commas, search terms embedded in each other, and slow searches due to the lack of indexes. Write a reusable piece of code to perform the comma-separate roll-ups for you so you don't keep reinventing the wheel.

这篇关于是否可以违反第一正常形式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！