Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <blockquote> <p>Is it ever a good idea ... that represent “all other records”?</p> </blockquote> <p>Is it it ever a good idea to represent a <strong>tree</strong> in table? <em>Root</em> of a tree represents “all other records”.</p> <p>Trees and hierarchies are not simple to work with, but there are many examples, articles and books that tackle the problem -- like Celko's <em>Trees and Hierarchies in SQL</em>; Karwin's <em>SQL Antipatterns</em>.</p> <p>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 <code>Global</code> from your example is just another <code>Location</code> (root of a tree), so when a new location is added, you may decide if it will be a child of the <code>Global</code> or not.</p> <hr> <p><strong>Facts</strong></p> <ul> <li><em>Location</em>(LocationID) exists.</li> <li><em>Location</em>(LocationID) is contained in <em>Parent Location</em>(LocationID).</li> <li><em>Article</em>(ArticleID) exists.</li> <li><em>Article</em>(ArticleID) is available at <em>Location</em>(LocationID).</li> </ul> <p><strong>Constraints</strong></p> <ul> <li><p><code>Each</code> <em>Location</em> is contained in <code>at most one</code> <em>Parent Location</em>. <code>It is possible that for some</code> <em>Parent Location</em>, <code>more than one</code> <em>Location</em> is contained in <code>that</code> <em>Parent Location</em>.</p></li> <li><p><code>It is possible that some</code> <em>Article</em> is available at <code>more than one</code> <em>Location</em> <code>and that for some</code> <em>Location</em>, <code>more than one</code> <em>Article</em> is available at <code>that</code> <em>Location</em>.</p></li> </ul> <hr> <p><strong>Logical</strong></p> <p><img src="https://i.stack.imgur.com/D5Ikm.png" alt="enter image description here"></p> <hr> <p>This way you can assign any location to an article -- but <strong>have to resolve it to the leaf level</strong> when needed.</p> <p>The hierarchy (tree) is here represented in the "naive way"; use closure table, nested sets or enumerated path instead -- or, if you like recursion...</p>
    singulars
    1. This table or related slice is empty.
    1. POIs it ever a good idea to have a record in a reference table in your database that represent "all other records"?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USDamir Sudarevic
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat does it mean when an article is associated with both a location and the location's parent?
      singulars
      1. PO
      1. USPhilip Kelley
    2. CO@PhilipKelley -- this is what **have to resolve it to the leaf level** means. Possible redundancy has to be dealt with, means that there is a `select distinct ArticleID, LocationID` ...
      singulars
      1. PO
      1. USDamir Sudarevic
    3. COparent location doesn't really fit as every other record will have the same parent . . i am all for storing trees in a db table but this is not really a tree . .
      singulars
      1. PO
      1. USleora
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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