Is it ever a good idea to have a record in a reference table in your database that represent "all other records"?

Question

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

1. Article table (id, name, content)
2. Location table (id, name)
3. ArticleLocation table (id, article Id, location 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.

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.

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.

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.

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

Answer 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.

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.)

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.

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.

Answer 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.

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.
• Location(LocationID) is contained in Parent Location(LocationID).
• Article(ArticleID) exists.
• Article(ArticleID) is available at 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...