Post reply

Querying database schema using graph tables

  • December 23, 2019 at 12:00 am

    #3706147

    Comments posted to this topic are about the item Querying database schema using graph tables

  • December 24, 2019 at 3:52 am

    #3710161

    Hi Mala,

    First of all, great article.  It can be used both as a great introduction to graph tables and to solve a fairly common problem.  Your order of revelation and the clarity of the code and their explanations is awesome.

    Shifting gears a bit and with the understanding that this next part has nothing to do with your fine article (or maybe it does but I don't know enough about graph tables to say so), it sounds to me like you can't find the shortest path without actually knowing the number of hops to make so you know how many times to self join a table.  With things kicking around like Map Quest and Google Maps on an almost casual and frequently unappreciated manner, it's just amazing to me that MS can't easily determine the shortest FK path between two tables, regardless of the number of hops, in such a limited domain of nodes as the tables in such a small database.  Like I said, I might be reading that wrong because I'm fairly new to graph tables and don't have much reason to do a deep dive on them.

    That, not withstanding, this is pretty interesting and you've inspired me to play a bit with such networks of nodes.

    Thank you again for taking the time to put this article together.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 24, 2019 at 1:14 pm

    #3710229

    Hi Jeff, thank you for your kind words, it always matters to hear good things from people who know what they do. Small correction though - it is possible to find A shortest path between two tables without knowing the number of hops - the problem with foreign keys though is that it may not be the path you are looking for, or the ideal one to write your queries based upon. Other full fledged graph engines give you ALL the possible shortest paths so that you can pick what is the right one for you. With SQL Server implementation, for whatever reason they just picked one to show and that isn't very useful in many cases. Of course if you have just one shortest path as it may be with some really simple schema, then this might prove sufficient. But it doesn't even with AdventureWorks. The only way to get the path you want now assuming there is more than one is to run two queries, one that shows the # of hops and a second that pulls with multiple instances based on the hops. (I have given this as an improvement suggestion along with other things to them, we have to wait and see if they do it or if the feature will be considered experimental and abandoned going forward, hard to say which way it will go).

  • January 2, 2020 at 1:29 pm

    #3711749

    Hi Mala,

    Very nice article, thanks so much.

    Would this model still be so simple to navigate when you'd have composite keys's? I've been writing a solution to answer similar questions without graphs. Its easy when all keys comprise just one attribute.

  • January 2, 2020 at 5:04 pm

    #3711893

    I guess it depends. You'd get one line back for every field that is part of the key and that can suck sometimes. It also is not the best solution when you have cyclical relationships between tables, with each table referencing the other. There are also situations when you need to tune the maxrecursion option to suit your needs. I guess all this adds up to a second post 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply