Databases
StackOverflow2013
Postsdbo
POPossible options for storing hierarchical data
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPossible options for storing hierarchical data
    primarykey
    data
    text
    <p>We need the ability to map various types of entities to different types of hierarchical data in our web application. As an example, consider an entity called <strong>Vendor</strong>. We need the ability to map each instance of vendor to geographical areas. Geographical areas are in the following hierarchy:</p> <ol> <li>Post code - most granular geographical area; example, <em>EC2</em></li> <li>Locality - formed of post codes; example, Kensington. Each post code will be part of exactly one locality, no more.</li> <li>Town - formed of localities; example London. Each locality will be part of exactly one town, no more.</li> <li>District - formed of towns; example, Columbia.</li> <li>Province - formed of districts (equivalent to a state in some countries); example, South Carolina.</li> <li>Region - formed of provinces; example, Northeast Provinces.</li> <li>Country - formed of regions.</li> <li>Zone - formed of countries; example Southeast Asia.</li> <li>Continent - formed of zones.</li> </ol> <p>We have a complete database of post codes, localities, towns, districts, provinces, regions, countries, zones and continents. This currently exists as tables in an RDBMS.</p> <p>Our use cases:</p> <ol> <li>Ability to associate a Vendor with multiple geographies, at any level. For example, we could map <em>Nestle</em> to <em>Mainland Europe</em> (a zone), <em>California</em> (a US province) and <em>Greater London</em> (a district in UK).</li> <li>Ability to exclude some parts of a geography from the mapping. For example, when mapping <em>Nestle</em> to <em>California</em>, we may want to exclude <em>San Diego</em>.</li> <li>If the composition of a geography changes, no changes should be required to the mappings of which the geography is a part. For example, if a post code is added to <em>Greater London</em>, the mapping with <em>Nestle</em> should not require a change.</li> <li>Ability to query the database for a <strong>Vendor</strong> and a geography level. For example, if we query the database for <em>Nestle</em> and <em>post codes</em>, we should get all the post codes for <em>Greater London</em>, <em>California</em> (minus post codes for <em>San Diego</em>) and <em>Mainland Europe</em>. If we query the database for <em>Nestle</em> and <em>countries</em>, we should get <em>UK</em> (country for <em>Greater London</em>), <em>US</em> and all countries in <em>Mainland Europe</em>.</li> </ol> <p>There are many such mappings with many different types of hierarchical data, this is just one of the requirements.</p> <p>Looking for suggestions on storing the hierarchical data and the mappings. Please do not post answers that involve storing the data and mappings in RDBMS tables, as I am already aware of the options using RDBMS.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USmanish
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COShould this question be tagged with database-design if you're not looking for a database solution?
      singulars
      1. POPossible options for storing hierarchical data
      1. USMartin Wilson
    2. COI am not looking for an RDBMS based solution. There can be other database solutions involving NoSQL databases like Neo4j, Redis, etc. or hierarchical databases.
      singulars
      1. POPossible options for storing hierarchical data
      1. USmanish
    3. COFair enough :-)
      singulars
      1. POPossible options for storing hierarchical data
      1. USMartin Wilson
 

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