Is it good to have a lookup table entry in your database that represents "all other records"?

Question

I have an asp.net-mvc site with a SQL Server backend. I simplify my situation in order to isolate and isolate the problem. I have 3 tables in DB

• Article table (id, name, content)
• Location table (id, name)
• ArticleLocation table (id, article identifier, location identifier)

On my website, when you create an article, you select from the multi-segment list the locations to which you want to send this article.

There are about 25 locations, so I discussed adding a new location called Global as a shortcut instead of having the person select 25 different items from the list. I could still do this as a label on the front, but now I am discussing whether there is an opportunity for this to flow through the backend.

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

Things that I can think about make me nervous:

• What if I create an article and choose a global, but then future in the future 3 new locations. Without this global installation, these 3 places will not receive the article, but in a new way, they will. I'm not sure which is better, since the second thing may be what you want, but its a little less explicit.

• I have a report requirement, I want to filter all articles that are global. Imagine that I need the article.IsGlobal () method. Right now, I think I could say that if the project has the same number of places, like all the entries in the location table, I could translate this into being considered global, but again, since people can add new locations, I feel that this approach is somewhat flaky.

Does anyone have any suggestions for this dilemma around creating entries in the reference data table that truly reflect “all entries”. Appreciate any advice

Answer 1

Upon request, here is my comment, able to respond. It is also an opportunity to expand it.

I will limit my response to a system with a single list of locations. I have done the business of corporate hierarchy: companies, departments, regions, states, counties, offices and employees, or some of them. He's getting ugly.

In the case of an OP question, it seems that adding the AllLocations bit to the Articles table makes the intention clear. Any article with the flag set to 1 appears in all places, regardless of when they were created, and does not need to be written to 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 places.

Implementation requires a bit more work. I would add INSERT and UPDATE triggers to the Article and ArticleLocation to apply the rule set either by the AllLocations bit and not the corresponding lines in ArticleLocation , or the bit is clear and the locations can be explicitly set. (It is a personal preference for the database to protect itself from "bad data" whenever it is practical for this.)

Depending on your needs, a table-oriented function is a good way to hide some of the dirty work, for example. dbo.GetArticleIdsForLocation( LocationId ) can handle the AllLocations flag. You can use it in stored procedures and ad-hoc requests prior to JOIN using Article . In other cases, an opinion may be appropriate.

Another feature that you can borrow (“Steal from friends!”) Is that the administrator’s landing page is an “exception” page. This is the place where I show things that range from massive flaming disasters to simple pecadillos. In this case, articles related to zero locations would qualify as non-critical, but worth checking.

Articles that appear explicitly in every place may be of interest to someone adding a new location, so I will probably have a webpage for that. Some of the articles may need to be updated to take into account the new location explicitly or be revised to change in all places.

Answer 2

Is it really a good idea ... which are "all the other records"?

Is it good to represent a tree table in a table? The root of the tree represents "all other entries."

Trees and hierarchies are not easy to work with, but there are many examples, articles and books that solve the problem - for example, Selco Trees and Hierarchies in SQL; Karwin SQL Antipatterns.

So, you actually have a hierarchy (maybe just a tree) - this can help approach the problem this way from the very beginning. Global from your example is just another Location (the root of the tree), so when a new location is added, you can decide whether it will be a child of Global or not.

---

Facts

• Location (LocationID) exists.
• The location (LocationID) is contained in the parent location (LocationID).
• There is an article (ArticleID).
• An article (ArticleID) is available at Location (LocationID).

Limitations

• Each Location is contained in at most one Parental 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 in that Location.

---

Logical

---

Thus, you can assign any place for the article, but you must allow it at the sheet level , if necessary.

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

Answer 3

TL; DR

In this case, as I understand it, I consider it a good idea to create a "global" location in the Location table. I definitely find it preferable to create a “global” flag in the article table.

---

"Is that a good idea ...?" this is not a question that we like to answer on SO. This is mainly a discussion point, not a Q&A, and we also have enough creativity in our community to come up with an example where “this” would be a good idea, regardless.

To your more specific question, how can I represent "all locations" in a database? It is a solution 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 an assistant that selects all current locations in the database.

Do you expect to have a hierarchy of locations?

The location of the real world has a significant hierarchy:

• Global
• Multinational (continent, trade block)
• A country
• Administrative region (state, province, canton, etc.).
• Town
• The neighborhood

If you think you want to be able to choose, say, a country rather than a global one, then implementing a hierarchical view such as Damir is the best way to go. However, if you are not sure that you will ever have any grouping other than Global, the hierarchical data structure currently works too much. All you have to do is make sure your current implementation has a transition path to a possible future hierarchical representation.

Global as a pseudo-location

If you want future locations to be included in the global network and not need a hierarchical location structure, my instinct, based on many years of experience, would be to create a “Global” as a pseudo-location. That is, Global will be one of the places in the Location table, but this will be of particular importance. This is definitely a compromise, but it has the advantage of not changing 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 checking some flags somewhere. (Or, if you like flags, you can add the pseudo-location flag to the Location table.)

When using Global as a location, additions or deletions to the Location table are processed automatically. The query for all global articles is simple: the same as the query for all articles for any other location. Reporting on articles by location is also straightforward, and global articles appear in reports in exactly the same way as elsewhere. You can also imagine the difference between a “global” article (all current and future locations) and an “all locations” article (all current locations, but not in future locations).

Selecting all the articles that should be visible in a certain place is a little more complicated, now it is a check against the "Global" as well as this location, but at least it checks 2 values ​​in one table and checks two different tables,

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

against

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

Answer 4

From the logic, as you described it, GLOBAL should be virtually global and remain global, even if you add new locations (problem 1 solved). But it also means that GLOBAL is not the same as “all locations” (as there may be other locations that we have not yet defined). I think that this logic is necessary, in particular, according to your requirement 2 - otherwise it will completely fail when adding new locations.

Analysis done! From the foregoing, we see that GLOBAL is something more than these places. It makes no sense to try to define it as a location. Go for the easiest solution!

• Article table (id, name, content, global )

i.e. boolean flag - the article is global or not. In the user interface, do this simply as a check box - if the check box is selected, the "Multi Selector" field will be disabled. Simple, easy, requirements are met. Done!

Answer 5

Is there a need to automatically add some articles to new locations when adding new places? If so, then Id would consider adding a new global property to the backend.

Otherwise, it is probably not worth the effort. Even if you had 10,000 articles and 20 different places selected for each article, it would be about 200 thousand records, which is not so bad when setting indexes.

Check existing data and see how people are already choosing locations. If most users choose only a few locations, and not all, then this is really the edge, and you should not work on it if it really creates problems.

Answer 6

I agree with @HABO's comment (he should have posted it, if he does, support him). Adding an attribute to a table. An article to identify those elements that should be associated with all locations, present and future, presumably throughout the life of the article, should save you time and effort in the long run. Of course, triggers and counts against everyone will do the trick, but they are awkward and will painfully support you if they come with a subsequent change in the system. The user interface will be easier to use, as the user just needs to click the check box (or something else), and not repeatedly click everything in the drop-down list of unexpected lengths.

(The idea of ​​Damir’s hierarchy will also work, but speaking of too much experience, it’s difficult to work with them, and I would not have presented them here if there weren’t much more system and / or business to get out of it.)