在数据库中的引用表中有一个表示“所有其他记录”的记录是一个好主意吗?

I have an asp.net-mvc website with a SQL Server backend. I am simplifying my situation to highlight and isolate the issue. I have 3 tables in the DB

我有一个asp.net-mvc网站和一个SQL Server后端。我正在简化我的处境，以突出和孤立这个问题。DB中有3个表

1. Article table (id, name, content)
2. 项目表(id、名称、内容)
3. Location table (id, name)
4. 位置表(id、名称)
5. ArticleLocation table (id, article Id, location Id)
6. ArticleLocation表(id、article id、位置id)

On my website, when you create an article, you select from a multiselect listbox the locations where you want that article sent.

在我的网站上，当你创建一篇文章时，你从一个多选择列表框中选择你想要那篇文章发送的位置。

There are about 25 locations so I was debating adding a new location called "Global" as a shortcut instead of having the person select 25 different items from a listbox. I could still do this as a shortcut on the front end but now I am debating if there is benefit for this to flow through to the backend.

大约有25个地点，所以我在考虑增加一个名为“Global”的新位置作为快捷方式，而不是让人从列表框中选择25个不同的项目。我仍然可以把它作为前端的快捷方式，但是现在我在争论是否有好处可以让它流到后端。

So if I have an article that goes global, instead of having 25 records in the ArticleLocation table, I would only have one and then I would do some tricks on the front end to select all of the items. I am trying to figure out if this is a very bad idea.

如果我有一篇文章是全球性的，而不是在ArticleLocation表中有25条记录，我只有一条然后我会在前端做一些技巧来选择所有的条目。我想弄清楚这是不是一个坏主意。

Things I can think about that are making me nervous:

我能想到的让我紧张的事情:

1. what if I create an article and choose global but then last in the future 3 new locations are added. Without this global setting, these 3 location would not get the article but in the new way, they would. I am not sure what is better as the second thing might actually be what you want but its a little less explicit.

   如果我创建了一篇文章并选择了global，但最后在未来的3个新位置中添加了什么呢?如果没有这个全局设置，这3个位置将不会获得文章，但以新的方式，它们会。我不确定什么更好，因为第二件事可能实际上是你想要的，但它不那么明确。

2. I have a requirement on a report, I want to filter by all articles that are global. Imagine I would need a article.IsGlobal() methode. Right now I guess I could say if a project has the same count of locations as all of the records in the location table I could translate that to being deemed global but again since people can add new locations, I feel like this approach is somewhat flaky.

   我对报告有一个要求，我想过滤所有的全球性文章。假设我需要一篇文章。isglobal()方法。现在我想我可以说，如果一个项目的位置计数与位置表中的所有记录相同，我可以将其转换为全局的，但是由于人们可以添加新的位置，我觉得这种方法有点不可靠。

Does anyone have any suggestions for this dilemna around creating records in a reference data table that really reflect "all records". Appreciate any advice

关于在引用数据表中创建真正反映“所有记录”的记录，有人对此有什么建议吗?欣赏任何建议

6 个解决方案

#1

By request, here is my comment promoted to an answer. It's an opportunity to expand on it, too.

根据我的请求，以下是我的回复。这也是一个扩展它的机会。

I'll limit my answer to a system with a single list of locations. I've done the corporate hierarchy thing: Companies, Divisions, Regions, States, Counties, Offices and employees or some such. It gets ugly.

我将把我的答案限制在一个只有一个位置列表的系统上。我做过公司层级的事情:公司，部门，地区，州，县，办公室和员工等等。它变得丑陋。

In the case of the OP's question, it seems that adding an AllLocations bit to the Articles table makes the intention clear. Any article with the flag set to 1 appears in all locations, regardless of when they were created, and need not have any entries in the ArticleLocation table. An article can still be explicitly added to all existing locations if the author does not want it to automatically appear in future locations.

在OP的问题中，似乎向Articles表中添加一个AllLocations位会使意图变得清晰。将标志设置为1的任何文章都将出现在所有位置，无论它们是在什么时候创建的，并且不需要在ArticleLocation表中包含任何条目。如果作者不希望文章自动出现在将来的位置，那么仍然可以显式地添加到所有现有位置。

Implementation involves a little more work. I would add INSERT and UPDATE triggers to the Article and ArticleLocation tables to enforce the rule that either the AllLocations bit is set and there are no corresponding rows in ArticleLocation, or the bit is clear and locations may be explicitly set. (It's a personal preference to have the database defend itself against "bad data" whenever it's practical to do so.)

实现需要更多的工作。我会添加插入和更新触发器文章和ArticleLocation表执行规则,要么AllLocations设置和没有ArticleLocation中相应的行,或者是明确的和位置可以显式地设置。(这是一个个人喜好的数据库抵抗“坏数据”每当实用。)

Depending on your needs, a table-valued function is a good way to hide some of the dirty work, e.g. dbo.GetArticleIdsForLocation( LocationId ) can handle the AllLocations flag internally. You can use it in stored procedures and ad-hoc queries to JOIN with Article. In other cases a view may be appropriate.

根据您的需要，表值函数是隐藏一些脏工作的好方法，例如dbo。GetArticleIdsForLocation(LocationId)可以在内部处理AllLocations。您可以在存储过程和特定查询中使用它来与Article连接。在其他情况下，视图可能是合适的。

Another feature that you are welcome to borrow ("Steal from your friends!") is to have the administrator's landing page be an "exceptions" page. It's a place where I display things that vary from massive flaming disasters to mere peccadillos. In this case, articles that are associated with zero locations would qualify as something non-critical, but worth checking up on.

欢迎您借用的另一个特性(“从您的朋友那里偷东西!”)是，管理员的登录页面是一个“例外”页面。我在这里展示各种各样的东西，从巨大的火灾到小小的小过失。在这种情况下，与零位置关联的文章将被认为是非关键的，但值得检查。

Articles that are explicitly shown in every location might be of interest to someone adding a new location, so I would probably have a web page for that. It may be that some of the articles should be updated to account for the new location explicitly or reconsidered for being changed to all locations.

在每个位置显式显示的文章可能会让添加新位置的人感兴趣，因此我可能会为此创建一个web页面。可能有些文章应该被更新，以明确地说明新位置，或者重新考虑将其更改为所有位置。

#2

Is it ever a good idea ... that represent “all other records”?

这是个好主意吗?表示“所有其他记录”?

Is it it ever a good idea to represent a tree in table? Root of a tree represents “all other records”.

在表中表示树是一个好主意吗?树的根表示“所有其他记录”。

Trees and hierarchies are not simple to work with, but there are many examples, articles and books that tackle the problem -- like Celko's Trees and Hierarchies in SQL; Karwin's SQL Antipatterns.

树和层次结构并不容易处理，但是有许多示例、文章和书籍可以解决这个问题——比如SQL中的Celko树和层次结构;Karwin反模式的SQL。

So what you actually have here is a hierarchy (maybe just a tree) -- it may help to approach the problem that way from the start. The Global from your example is just another Location (root of a tree), so when a new location is added, you may decide if it will be a child of the Global or not.

所以你在这里实际上有一个层次结构(也许只是一个树)——它可能有助于从一开始就解决这个问题。示例中的全局变量只是另一个位置(树的根)，因此当添加一个新位置时，您可以决定它是否是全局变量的子位置。

---

Facts

事实

• Location(LocationID) exists.
• 位置(LocationID)存在。
• Location(LocationID) is contained in Parent Location(LocationID).
• Location(LocationID)包含在父位置(LocationID)中。
• Article(ArticleID) exists.
• 文章(正如)存在。
• Article(ArticleID) is available at Location(LocationID).
• 文章(Article id)可以在Location(LocationID)中找到。

Constraints

约束

• Each Location is contained in at most one Parent Location. It is possible that for some Parent Location, more than one Location is contained in that Parent Location.

  每个位置最多包含在一个父位置中。对于某些父位置，可能在该父位置中包含多个位置。

• It is possible that some Article is available at more than one Location and that for some Location, more than one Article is available at that Location.

  有可能有些文章可以在多个位置获得，而对于某些位置，可以在该位置获得多个文章。

---

Logical

逻辑

---

This way you can assign any location to an article -- but have to resolve it to the leaf level when needed.

通过这种方式，您可以为文章分配任何位置——但必须在需要时将其解析为叶子级别。

The hierarchy (tree) is here represented in the "naive way"; use closure table, nested sets or enumerated path instead -- or, if you like recursion...

层次(树)在这里用“朴素的方式”表示;使用闭包表、嵌套集或枚举路径——或者，如果您喜欢递归……

#3

tl;dr

In this case as I understand it, I think it is a good idea to create a "global" location in the Location table. I definitely find it preferable to creating a "global" flag in the Article table.

在这种情况下，正如我所理解的，我认为在位置表中创建一个“全局”位置是一个好主意。我绝对觉得在文章表中创建“全局”标志更好。

---

"Is it ever a good idea...?" is not a question we like to answer on SO. It's mostly a debate question, not a Q&A question, and besides, we have enough creativity in our community to come up with some example where "it" would be a good idea, regardless.

“这是个好主意吗?”这不是我们喜欢回答的问题。这主要是一个辩论问题，而不是问答问题。此外，我们在社区中有足够的创造力来想出一个“它”是个好主意的例子，无论如何。

To your more specific question, how do I represent "all locations" in the database? that is a judgement call based on your business requirements.

对于您更具体的问题，我如何表示数据库中的“所有位置”?这是基于您的业务需求的判断调用。

Do you want "all locations" to include future locations?

If not, then probably you should only implement "all locations" as a helper that selects all current locations in the database.

如果不是，那么您可能应该只实现“所有位置”，作为选择数据库中所有当前位置的助手。

Do you anticipate having a hierarchy of locations?

Real-world locations have significant hierarchy:

真实世界的地点有重要的层次:

• Global
• 全球
• Multi-national (continent, trading block)
• 跨国(大陆,交易块)
• Country
• 国家
• Administrative region (state, province, canton, etc.)
• 行政区域(州、省、州等)
• City
• 城市
• Neighborhood
• 社区

If you think you are going to want to have the option to choose, say, a Country, instead of Global, then implementing a hierarchical representation such as Damir suggests is the best way to go. However, if you are not sure if you are ever going to have any other grouping of locations besides Global, a hierarchical data structure is too much work for now. All you need to do is make sure your current implementation has a migration path to a possible future hierarchical representation.

如果你认为你想要有选择的选项，比如说，一个国家，而不是全球，那么实现一个分层的表示，如Damir建议是最好的方法。但是，如果您不确定是否还会有除全局外的任何其他位置分组，那么现在的分层数据结构就太麻烦了。您所需要做的就是确保您当前的实现具有可能的未来分层表示的迁移路径。

Global as a pseudo-location

If you do want future locations included in Global and do not need a hierarchical location structure, then my instinct based on years of experience would be to create "Global" as a pseudo-location. That is, Global would be one of the locations in the Location table, but it would have a special meaning. This is definitely a trade-off, but has the benefit of not altering the data structure to support Global which means that all the special cases that "Global" creates are handled by excluding or including some Locations in queries rather than by checking some flags somewhere. (Or if you like flags, you can add a 'pseudo-location' flag to the Location table.)

如果你希望未来的地点包含在全局中，而不需要分层的位置结构，那么基于多年的经验，我的直觉将是创建“全局”作为伪位置。也就是说，Global将是Location表中的一个位置，但是它将具有特殊的意义。这当然是一种权衡，但好处是不改变数据结构以支持全局，这意味着“全局”创建的所有特殊情况都是通过排除或包含查询中的某些位置来处理的，而不是通过检查某个地方的某些标志来处理的。(或者如果您喜欢标志，可以向位置表添加“伪位置”标志。)

With Global as a location, additions or deletions to the Location table are handled automatically. The query for all Global articles is straightforward: the same as the query for all articles for any other Location. Reporting on articles by location is also straight forward, with Global articles appearing in reports just like any other location. You can also represent the difference between a "Global" article (all current and future locations) and an "all locations" article (all current locations but no future locations).

使用全局变量作为位置，将自动处理对位置表的添加或删除。所有全局项目的查询都很简单:与任何其他位置的所有项目的查询相同。按位置报道文章也很直接，在报告中出现的全球文章和其他地方一样。您还可以表示“全局”文章(所有当前和未来的位置)和“所有位置”文章(所有当前的位置，但没有未来的位置)之间的区别。

Selecting all articles that should be visible at a specific location is slightly harder, it's now a check against "Global" as well as that location, but at least it is checking for 2 values in the same table versus checking two different tables.

选择应该在特定位置可见的所有文章稍微困难一点，它现在是对“全局”和那个位置的检查，但至少它是检查同一个表中的两个值，而不是检查两个不同的表。

SELECT article_id FROM ArticleLocation WHERE location_id in (1, 5);

vs

vs

SELECT article_id FROM ArticleLocation WHERE location_id = 5
UNION
SELECT id FROM Article WHERE is_global;

#4

From the logic, as you described it, GLOBAL should be actually global and stay global, even if you add new locations (problem 1 solved). But this also implies that GLOBAL is not the same thing as "all locations" (as there might also exist some other locations we don't have defined yet). I think this logic is needed especially by your requirement 2 - otherwise it would completely fail on adding new locations.

从逻辑上来说，正如您所描述的，全局应该是全局的，并且是全局的，即使您添加了新的位置(问题1解决了)。但这也意味着全局变量与“所有位置”并不相同(因为也可能存在一些我们尚未定义的其他位置)。我认为这一逻辑是必需的，尤其是您的需求2——否则在添加新位置时会完全失败。

Analysis done! From the above we see that GLOBAL is something above all those locations. There's no sense in trying to define it as a Location. Go for the easiest solution!

分析完成了!从上面我们看到，GLOBAL是所有这些位置之上的东西。把它定义为一个位置是没有意义的。选择最简单的解决方案!

• Article table (id, name, content, global)
• 项目表(id、名称、内容、全局)

i.e. boolean flag - article is global or not. In the UI, do it simply as a checkbox - if checked, the multiselect box will be disabled. Simple, easy, requirements met. Done!

即布尔标志-物品是否全局。在UI中，只需将其作为复选框—如果选中，将禁用multiselect框。简单,简单,需求得到满足。完成了!

#5

Is there a need to automatically add some articles to new locations when new locations are added? If yes then in such case I’d consider adding new ‘global’ property in the backend.

是否需要在添加新位置时自动添加一些文章到新位置?如果是，那么在这种情况下，我将考虑在后端添加新的“全局”属性。

Otherwise it probably isn’t worth the effort. Even if you had 10000 articles and 20 different locations selected for each article that would be about 200k records which is not that bad when you set indexes.

否则它可能不值得付出努力。即使你为每一篇文章选择了10000篇文章和20个不同的位置，也会有大约200k的记录，这在你设置索引的时候并没有那么糟糕。

Check your existing data and see how people are already choosing locations. If most users select only several locations and not all then it’s really an edge case and you shouldn’t be working on it unless it really creates problems.

检查现有的数据，看看人们是如何选择地点的。如果大多数用户只选择了几个位置，而不是全部，那么这真的是一个边缘情况，除非它真的会产生问题，否则你不应该去处理它。

#6

I agree with @HABO's comment (he should have posted it--if he does, upvote him). Adding an atrribute to table Article to identify those items that are to be associated with all Locations, present and future, presumably for the lifetime of the article, should save you time and effort over the long run. Sure, triggers and counts-against-all will do the trick, but they're awkward and would be a pain to support if/when subsequent system changes come along. The UI would be simpler to use, as the user just has to click a checkbox (or whatever) and not multi-click everything in a dropdown of unforseeable length.

我同意@HABO的评论(他应该把它贴出来——如果他这么做了，就给他投赞成票)。在表文章中添加一个atrribute，以标识与所有位置、当前和未来(可能在文章的整个生命周期内)相关的项目，从长远来看，应该可以节省您的时间和精力。当然，触发器和计数—所有的触发器都会起作用，但是它们是笨拙的，如果/当随后的系统更改出现时，支持它们将是一件痛苦的事情。UI使用起来会更简单，因为用户只需单击一个复选框(或其他)，而不必在不可预见长度的下拉菜单中多次单击所有内容。

(@Damir's hierarchy idea would work as well, but--speaking from a bit too much experience--they're a hassle to work with, and I wouldn't introduce one here unless there was significantly more system and/or business use to get out of it.)

(@Damir的层次结构想法也可以，但从太多的经验来看，这是一种麻烦，我不会在这里介绍它，除非有更多的系统和/或业务用途可以从中获益。)