Databases
StackOverflow2013
Postsdbo
POIs it ever a good idea to have a record in a reference table in your database that represent "all other records"?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs it ever a good idea to have a record in a reference table in your database that represent "all other records"?
    text
    copied!<p>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</p> <ol> <li>Article table (id, name, content)</li> <li>Location table (id, name)</li> <li>ArticleLocation table (id, article Id, location Id)</li> </ol> <p>On my website, when you create an article, you select from a multiselect listbox the locations where you want that article sent.</p> <p>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.</p> <p>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.</p> <p>Things I can think about that are making me nervous:</p> <ol> <li><p>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.</p></li> <li><p>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.</p></li> </ol> <p>Does anyone have any suggestions for this dilemna around creating records in a reference data table that really reflect "all records". Appreciate any advice </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload