Problems when generating flowcharts from a database table

When I started with the following idea, I thought that GraphViz is the perfect tool to realize it, however the results, which I got so far are not totally satisfying. But maybe there are some parameters in GraphViz, which I didn't find and which can improve the result of my scripts.

I would like to create process flowcharts based on entries in a database table. So each row in the table describes a node in the flowchart and it has the information, which node is the next one or – in the case of a decision node – the next ones in the cases of "yes" or "no".

A simplified example, created with an Oracle database may look like this:

create table pr
(pr_id number(10),
pr_step number(10),
pr_symbol varchar2(20),
pr_text varchar2(50),
pr_owner varchar2(20),
pr_next_yes number(10),
pr_next_no number(10));

insert into pr values (1, 1,'circle','(1) Start',null,2,null);
insert into pr values (1, 2,'box','(2) This is the first step','Department 1',3,null);
insert into pr values (1, 3,'box','(3) Check the result','Department 1',4,null);
insert into pr values (1, 4,'hexagon','(4) Is the result OK?','Department 1',5,6);
insert into pr values (1, 5,'box','(5) Give it to next department','Department 1',7,null);
insert into pr values (1, 6,'hexagon','(6) Can the problem be fixed?','Department 1',8,9);
insert into pr values (1, 7,'box','(7) This is the second step','Department 2',10,null);
insert into pr values (1, 8,'box','(8) Repair it','Department 1',3,null);
insert into pr values (1, 9,'box','(9) Waste it!','Department 3',13,null);
insert into pr values (1,10,'box','(10) Check the result','Department 2',11,null);
insert into pr values (1,11,'hexagon','(11) Is the result OK?','Department 2',12,9);
insert into pr values (1,12,'box','(12) Success!',null,13,null);
insert into pr values (1,13,'circle','(13) End',null,null,null);

commit;

And this could be the SQL query, to create a script for dot with the entries of this table:

with
b as
(select
1 o1,
'Digraph G {splines=ortho;node [fontsize=11 fontname=helvetica];edge [fontsize=9 fontname=helvetica];ratio="fill";size="8.3,11.7!";margin=0;ranksep = 0.5;' b_row
from dual
union
select
2,
'subgraph cluster_c1 {label = "";style = invis; '||
listagg('e'||to_char(pr_step),',') within group (order by pr_step) ||'}'
from pr
where pr_symbol <> 'circle'
and pr_id = :process
union
select
3,
'e'||to_char(pr_step)||' [shape='||pr_symbol||
', label=<'||
pr_text||
''||
case when pr_owner is not null
then ''||
to_char(pr_owner)||
''
else '' end ||
'>];' b_row
from pr
where pr_id = :process
union
select
4,
'e'||to_char(pr_step)||'->'||'e'||to_char(pr_next_yes)||case when pr_next_no is not null then ' [taillabel="Yes", labeldistance = 1.5];' else ';' end
from pr
where pr_next_yes is not null
and pr_id = :process
union
select
4,
'e'||to_char(pr_step)||'->'||'e'||to_char(pr_next_no)||' [taillabel="No", labeldistance = 1.5];'
from pr
where pr_next_no is not null
and pr_id = :process
union
select
9 o1,
'}' b_row
from dual)
select b_row from b
order by o1;

With this SQL-query I get the following script:

Digraph G {splines=ortho;node [fontsize=11 fontname=helvetica];edge [fontsize=9 fontname=helvetica];ratio="fill";size="8.3,11.7!";margin=0;ranksep = 0.5;
subgraph cluster_c1 {label = "";style = invis; e2,e3,e4,e5,e6,e7,e8,e9,e10,e11,e12}
e11 [shape=hexagon, label=<(11) Is the result OK?Department 2>];
e12 [shape=box, label=<(12) Success!>];
e13 [shape=circle, label=<(13) End>];
e9 [shape=box, label=<(9) Waste it!Department 3>];
e3 [shape=box, label=<(3) Check the resultDepartment 1>];
e4 [shape=hexagon, label=<(4) Is the result OK?Department 1>];
e5 [shape=box, label=<(5) Give it to next departmentDepartment 1>];
e6 [shape=hexagon, label=<(6) Can the problem be fixed?Department 1>];
e7 [shape=box, label=<(7) This is the second stepDepartment 2>];
e8 [shape=box, label=<(8) Repair itDepartment 1>];
e10 [shape=box, label=<(10) Check the resultDepartment 2>];
e1 [shape=circle, label=<(1) Start>];
e2 [shape=box, label=<(2) This is the first stepDepartment 1>];
e1->e2;
e10->e11;
e11->e12 [taillabel="Yes", labeldistance = 1.5];
e11->e9 [taillabel="No", labeldistance = 1.5];
e12->e13;
e2->e3;
e3->e4;
e4->e5 [taillabel="Yes", labeldistance = 1.5];
e4->e6 [taillabel="No", labeldistance = 1.5];
e5->e7;
e6->e8 [taillabel="Yes", labeldistance = 1.5];
e6->e9 [taillabel="No", labeldistance = 1.5];
e7->e10;
e8->e3;
e9->e13;
}

In the resulting graph I have the following ideas for improvement:

1. I would like to connect the "outgoing" edges of a node always to the "East"-, "West"- or "South"-port of the node, like it is in (4). But I don't want to make it fix, like "yes" is always "South" and "no" on the "East"-side. So I need something like "node:(e|w|s)" with | meaning "or".
2. Similar the incoming edges should be "East", "West" or "North", but never "South".
3. As a result the edge from node (8) to (3) would connect both nodes on the "West" side.
4. The nodes (5), (7), (10) and (11) should be "centered"
5. The line from (11) to (9) should go first to the "West" and then to the "South" instead of vice versa.
6. The white space above and below the text in the hexagons is too large. I can reduce it with width, height and fixedsize, but I also want the size of the hexagons dependent on the length of the text. Why is it not similar to the boxes as the space in the "triangles" at the left and the right side of the hexagon is not used for the text?

What of my wishes can be achieved with the possibilities of GraphViz, not by editing the resulting script for a single node but with adding general parameters and rules in the SQL query, that apply to all nodes or to all nodes that fulfill a certain condition?

Add

This approach is too complex. First create a flowchart diagram, then start coding for that specific chart which allowes you to work on. Draw something using a online diagramming tool like Creately flowchart software and convert

Most of these are reasonable

Most of these are reasonable features that are not currently supported.

I can help some with 4) and 6). For 4) increase the edge weights. For 6), unless you specify fixedsize, the node is going to be made big enough to contain the label, and also be at least width x height. Part of the reason the "triangles" aren't used is because there is also a default margin around labels. Try setting margin=0 for the hexagons and see what happens.

Recent comments