ERD: Improve auto layout

[adityatoshniwal]

Describe the solution you'd like
Currently, the auto layout in ERD places the nodes correctly but links are:

1. Not always right angled.
2. Goes behind the nodes.
3. Does not switch sides to get best link route.

This can be improved by using a better algorithm.

[wayland]

An example of the lines not going anywhere very sensible. I'd personally call this a bugfix rather than a feature, though it's an aesthetic bug rather than a functional one.

HTH

[wayland]

May also be related to #5199

[morrissimo]

seeing identical behavior on pgadmin 7.8 on Mac - after generating an ERD for an existing database, not only do the relationship lines look pretty wild, but they also have anchor points that don't reset and aren't movable/editable.

Note that this seems to be the only issue preventing an autogenerated ERD from being useful - everything else looks pretty good!

[raprocks]

Any progress on this or can someone point me in the direction where i can start working on it?

[adityatoshniwal]

Hi @raprocks,
Right now, we're failing to find a relevant layout algorithm. Basically, a layout algo will help in getting the positions of nodes and links. We're using dagre currently https://www.npmjs.com/package/dagre

[carlo-salinari]

For comparison, I'm attaching an auto-generated er diagram of the same (very simple) db done with pgAdmin4 v7.8 and a proprietary software (Navicat Data Modeler 3).

I'm no expert, but maybe a first step in the right direction could be switching to RightAngleLinks as per @projectstorm/react-diagrams demo.

(I'm with @wayland in that this looks more like a bugfix than a feature-request. I do recognize that automatic laying-out is very complex to get right, and will probably require many iterations).

[adityatoshniwal]

@carlo-salinari The major difference between pgAdmin ERD and the ERD which you shared is - The links in pgAdmin start from the actual column used, and not just randomly sticking the link anywhere.
If we're OK to sacrifice this then a solution can be worked on.

[carlo-salinari]

@adityatoshniwal I believe from the end-user perspective the major difference is in terms of clarity, so to speak.

And that seems to come from the routing around (and not behind) the tables, better use of spacing and grouping, predictability of the links, overall less graphical noise from the background and better color contrast.

The direct connection to the column certainly complicates routing a bit, but it could probably be maintained. It would be interesting to find comparisons.

[adityatoshniwal]

@carlo-salinari I had tried to find a suitable algorithm to keep links on column and auto layout. But unfortunately didn't find anything concrete. I think at some point we'll have to let it go in favour of better clarity. In future if we find a suitable algo then we can link back to columns as an improvement.

[carlo-salinari]

@adityatoshniwal You mean let go of the link-to-key constraint? That's something to experiment with, but I don't think that's the crux of the problem here.

Automatic graph layout is a notoriously hard problem: there is no clearly understood metric to evaluate the results and the complexity varies wildly with graph input size and constraints number.

These two aspects will keep coming up while looking for a solution (even a partial one).

It's unlikely that we'll be able to find a ready-made robust algorithm able to handle arbitrarily sized schemas right away, even removing the link-to-key constraint.

A more pragmatic approach would be to work step-wise, i.e.:

1. establish thresholds (small, medium, large schemas) and gather sample schemas
2. work towards making automatic layout of small/medium schemas mostly work right out of the box (experiment with and fine-tune some classic force directed/network flow algorithm)
3. make fixing schemas by hand as painless as possible (right now it's a pretty frustrating experience)

This seems more or less the approach currently followed by all other similar applications.

And eventually some magic machine-learning solution will materialize, capturing all the aesthetic requirements :)

[carlo-salinari]

This is a sample of an auto-generated erd graph with maintained link-to-key constraints (https://www.softwareideas.net/en/erd-auto-generator).

• layout is orthogonal
• links route around tables
• bends variance appear uniform
• crossings are marked graphically
• not much in the way of compaction

[carlo-salinari]

Only javascript library I could find tackling erd diagrams decently: https://gojs.net/latest/samples/entityRelationship.html

Unfortunately, I don't think their license is compatible with pgAdmin's.

[wayland]

Other people (eg. DataWharf) seem to be using maxGraph (basically mxGraph, but gets updates). They're under an Apache-style license; no idea about compatibility.

https://github.com/maxGraph/maxGraph

HTH,

[javinosearas]

It would be very helpful to add a feature to manually move the nodes of the lines manually by clicking and dragging. That way it would be a lot easier to tidy up an auto generated ERD. The ability to manually insert and delete nodes on a line segment would also come in handy.

[RieBi]

This is an interesting example where the lines are made from column to column and everything looks quite great. It's DrawSQL, a commercial website.

[RieBi]

It seems like orthogonal lines is the way

[adityatoshniwal]

@RieBi ERD tool lines works fine when building from scratch. The issue is with auto layout. I will go through all the suggestions and see how we can fix it.

[vishnupeas]

This is clearly a 'bug' not a 'feature' from end user perspective. Also this issue should get a better priority imo.

[adityatoshniwal]

This is clearly a 'bug' not a 'feature' from end user perspective. Also this issue should get a better priority imo.

Feel free to raise a PR if you have a solution. I've spend a lot of time in past, may be there is one available today.

[adityatoshniwal]

Point no.1 and 3 will be fixed with my PR here - #8167.
For point no. 2, I spent a lot of time to find a apt. path finding algorithm.
I tried A-star but it becomes very very slow for large diagrams - and produces links with lot of turns. If anybody happens to know a good path finding lib then we can use that. Intention is not to get the best optimal path but to get a path with least turns without going below nodes.

[pravesh-sharma]

Point no. 1 and 2 has been fixed and verified in snapshot build.

For point no. 2 we will create a new issue.

[adityatoshniwal]

For point no. 2 we will create a new issue.

Opened #8212