How to properly query a "Matryoshka-like" database?



  • What is the most efficient, growth-proof and professional way to query for a whole hierarchy of data in a database?

    For instance:

    table country(country_id primary);
    table state(state_id primary, country_id foreign);
    table city(city_id primary, state_id foreign);
    table street(street_id primary, city_id foreign);
    

    Examining common programming patterns (i.e. Active Record) gives the impression that getting each parent row in a separate query is the standard way to do it.

    But like someone said somewhere in these forums, it's like "using the same door knob 400 times".

    So I tend to believe that using a join between all tables is better, and always feel a little uncomfortable when writing something that fits in this case because this questioning always comes to mind.

    p.s.: Yes I'm kind of a hobbyist. I absolutely enjoy computer programming, but my education is in Botany.


  • Discourse touched me in a no-no place

     Have you considered de-normalisation? (In fact, I'm finding it difficult to see how you could come up with your (presumably contrived) example using normalisation in the first place.)



  • @cangulo said:

    What is the most efficient, growth-proof and professional way to query for a whole hierarchy of data in a database?

    For instance:

    table country(country_id primary);
    table state(state_id primary, country_id foreign);
    table city(city_id primary, state_id foreign);
    table street(street_id primary, city_id foreign);

    Look up OLAP and data cubes.



  • @cangulo said:

    Examining common programming patterns (i.e. Active Record) gives the impression that getting each parent row in a separate query is the standard way to do it.

    But like someone said somewhere in these forums, it's like "using the same door knob 400 times".

    So I tend to believe that using a join between all tables is better, and always feel a little uncomfortable when writing something that fits in this case because this questioning always comes to mind.

     

    IMHO a join is always better, although I've heard other opinions mostly by RoR users ...

    Advantages of using joins :

    - Eliminates communication overhead

    - Better query caching

    ... 

    Disadvantages:

    - Data duplication, several columns have the same data in multiple rows

    ... 


    In the company I work for, we usually create views where we join the tables and then do simple selects to get the data ...

    In several projects I've worked on, this thing sped up queries significantly  ...



  •  As part of my thesis I had a similar situation and did resolve it with JOINs as it was easy to handle, but I looked very seriously at this approach:

    http://www.dbmsmag.com/9603d06.html

    http://www.dbmsmag.com/9604d06.html

    http://www.dbmsmag.com/9605d06.html

    http://www.dbmsmag.com/9606d06.html

    where the author deals with tree structures in databases (not just linear). As it's part of a column, there's sometimes other stuff he talks about, so you have to jump to the right sections.

     

    HTH 


Log in to reply