StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>By request, here is my comment promoted to an answer. It's an opportunity to expand on it, too.</p> <p>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.</p> <p>In the case of the OP's question, it seems that adding an <code>AllLocations</code> bit to the <code>Articles</code> 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 <code>ArticleLocation</code> 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.</p> <p>Implementation involves a little more work. I would add <code>INSERT</code> and <code>UPDATE</code> triggers to the <code>Article</code> and <code>ArticleLocation</code> tables to enforce the rule that either the <code>AllLocations</code> bit is set and there are no corresponding rows in <code>ArticleLocation</code>, 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.)</p> <p>Depending on your needs, a table-valued function is a good way to hide some of the dirty work, e.g. <code>dbo.GetArticleIdsForLocation( LocationId )</code> can handle the <code>AllLocations</code> flag internally. You can use it in stored procedures and ad-hoc queries to <code>JOIN</code> with <code>Article</code>. In other cases a view may be appropriate.</p> <p>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.</p> <p>Articles that are <em>explicitly</em> 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.</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