Find possible paths between two tables

[loicknuchel]

The idea behind this feature is sometimes you want to join two tables but they don't have a direct foreign key and you don't know which joins (path) to use for this. Azimutt could help you by searching in table relations which ones you can use for this.

This feature is available but is still experimental (look at "Find path" button in the Menu, accessible with a click on the top left logo).

The challenge

This feature is not as straightforward as it may seem, here is why...

1. We are not looking for the shortest path but all the possible paths. This is because we want relevant paths from the business point of view, not just any technically working path.
   Foreign key relations can be followed in both directions and often database schema have a table linked to many others (ex: users or accounts via a created_by column) so it acts as a huge hub for any path, vastly increasing the number of possible paths and making the shortest ones often irrelevant.

2. Some joins are possible even if there is no foreign key (look at workpress schema ^^). For example, Rails Polymorphic Associations store a type column along with the id one, telling which table should be joined with the id. Foreign keys does not support that so no foreign key is declared. So for now, if there is no foreign key, there is no relation for us, so we are quite blind on this.

Current solution

1. There is some settings allowing you to ignore the tables and columns you want/need. So you can define your "not relevant" relations and then have much less paths, especially irrelevant ones.

2. No solution except manually adding fake foreign keys before importing your schema to Azimutt.

Possible improvements

1. Having some settings is not user friendly, especially if they must be filled to have a relevant feature. It would be much better if this worked out of the box "as the user expected".
   For that we can add default fields to ignore (such as created_by, updated_by, deleted_by and their many variants) as a first step.
   But still, there is some irrelevant results such as "doing a detour", if you could think of good heuristics for this, we will be happy to discuss them.

2. We could add some "manual relations" from the UI, so you could define possible joins (maybe with some properties) and then use them in the path finding algorithm.

We will be happy to have your thoughts on this, so let's discuss just below ⬇️

[matthiashhh]

Great feature, and a potentially a major selling point for Azimutt! One thing I miss (or at least can't find): A button to add all tables in a found path to the canvas with one click.

[loicknuchel]

Hi @matthiashhh

Thanks for the feedback and quite happy you like this feature 😄
I always dreamed of it, and I had to code my own tool to finally have it 🤣

You're right, it miss the "add all" tables to the layout. Will add it!