Re: How to store complex graph paths in mysql?

[Ivan Evstegneev]

Hello Peter,

I'm really appreciate your help. ^_^


>But you've confused me again. Do "edge_id node_1_id node_2_id" refer to column names.

Ok, a bit more explanation.

I've build DB sketch which consists of couple of tables (I won't list them all, just those which are relevant for explanation)

So, the first one is "Nodes" table. This table consists of two columns named "node_id"(PK) and "node_type"(doesn't affect the paths).

This looks like this:

node_id node_type
101 1
104 1
118 2
...

For my edges I have separate table named "Edges" ^_^

Among others it has columns such as: "edge_id"(PK), "spans_quantity" and others.

"Nodes" and "Edges" have many-to-many relationship.


Further more I've built separate table for fibers (table named Fibers) and "Amplifiers" these two tables are related with "Edges" with one-to-many (many amps or fibers per single edge) relationship. The same way I've connected "Ports" table with "Nodes".


Then I've asked myself, how would I store information about paths that my algorithm will find?

One of insights that I got (a half hour ago ))) ) is just to create the table named "Paths" which would consist of two rows: "items" and "values".

In such a case a calculated path should look like this:

items values
node 101
tx_id 2000
port_id 17
fiber_id 410
span_id 512
amp_id 618
fiber_id 412
span_id 545
amp_id 667
fiber_id 415
span_id 587
port_id 43
rx_id 1020
node_id 102

This may fully describe path between two nodes. Is it human readable? Yes (IMO).
But, is it good way to store such paths in MySQL?
I don't know... (I suppose this is my question after all, please don't blame me)

Of course I can add some more designations for example: in case I have three fibers along the way I'll name them fiber_1_id, fiber_2_id and fiber_3_id.Where each one of them will have its value. So do the others.

So on the one hand I've eliminated the problem of the same_named columns, but is it comfortable and more importantly "the right" way to store my paths like this?

On the other hand, I think that I've got confused between the "human readable representation of the paths" and the "database structure organization" and by the way I've build my tables (and relations between them) I've already implemented this hierarchical structure of paths. So I don't need to any additional work here.

Is it seems logical?


Thanks again.

Ivan