Databases
StackOverflow2013
Postsdbo
POModelling country adjacency in SQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POModelling country adjacency in SQL
    primarykey
    data
    text
    <p>I'm trying to model which countries border each other in MySQL. I have three tables:</p> <pre><code>nodes ----- node_id MEDIUMINT countries --------- country_id MEDIUMINT (used as a foreign key for nodes.node_id) country CHAR(64) iso_code CHAR(2) node_adjacency -------------- node_id_1 MEDIUMINT (used as a foreign key for nodes.node_id) node_id_2 MEDIUMINT (used as a foreign key for nodes.node_id) </code></pre> <p>I appreciate the nodes table is redundant in this example, but this is part of a larger architecture where nodes can represent many other items other than countries.</p> <p>Here's some data (IDs (which appear in all three tables) and countries)</p> <pre><code>59 Bosnia and Herzegovina 86 Croatia 130 Hungary 178 Montenegro 227 Serbia 232 Slovenia </code></pre> <p>Croatia is bordered by all the other countries, and this is represented in the node_adjacency table as:</p> <pre><code>59 86 86 130 86 178 86 227 86 232 </code></pre> <p>So Serbia's ID may appear as a <code>node_id_1</code> or a <code>node_id_2</code>. The data in this table is essentially non directed graph data.</p> <p>Questions:</p> <p>Given the name 'Croatia', what SQL should I use to retrieve its neighbours?</p> <pre><code>Bosnia and Herzegovina Hungary Montenegro Serbia Slovenia </code></pre> <p>Would there be any retrieval efficiency gains in storing the adjacency information as directed graph data? E.g. Croatia borders Hungary, and Hungary borders Croatia, essentially duplicating storage of the relationships:</p> <pre><code>86 130 130 86 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USjetboy
    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. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

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