Question about database structure for network visualuzation

Question

Hello folks. I have a matrix I created in Excel that shows 17 nodes, each node's connection to every other node (if any), and that connections strength. Note the error in the image, cells that represent a node's connection to itself should be null not 0.

Where:

0 no connection 1 min connection 2 median connection 3 primary connection

I want to translate this matrix into a database I can use for network visualization but I need help with the structure.

I'd like to avoid having a record for each connection as that's 278 records for my limited dataset alone (final dataset will have 200+ nodes). Would like to have a record for each node that contains every connection and it's connection strength.

Ideal database would look like (columns) Id Name Connections Strengths (where i2,3,4 = connections i2,3,4)

I've done this type of thing before with data trees and XML but looking for a more robust option I can query for online interactive graphics and games.

In terms of translating the Excel file to the database itself I was planning on writing a string parser to convert CSV to SQL but interested to hear if there are other ideas out there.

Thanks!

Answer 1

The common way of representing network (or graphs) in a tabular / relational structure is to divide it into two tables:

1) A node table where each node has a unique numeric identifier, together with any attributes. Something like this:

CREATE TABLE net_nodes (
  node_id         NUMBER PRIMARY KEY,
  node_type       ....
  ... other attributes
);

2) A links table to represent the connections. Each link has a unique numeric identifier and the IDs of the origin and destination nodes. And of course any attributes, such as direction/orientation (does not apply in your case), throughput, etc. Something like this:

CREATE TABLE net_links (
  link_id         NUMBER PRIMARY KEY,
  start_node_id   NUMBER NOT NULL REFERENCES net_nodes,
  end_node_id     NUMBER NOT NULL REFERENCES net_nodes,
  link_type       ....
  active          ....
  ... other attributes
);

That data model then lets you perform simple graph traversals using recursive SQL. That should work fine given you have a tiny network (you mention 200 nodes). For more serious graphs (10s or 100s of nodes or more) specialized graph databases / engines become necessary.

Answer 2

I think that this is a classic example of a many-to-many relationship.
You should solve it with three tables, for example:

1. Connection (Id, Name)
2. ConnectionToCharacteristicsLink (IdConnection, IdCharacteristics)
3. Characteristics (Id, Strength)

In your case, this can be done with 2 tables:

1. Node (NodeID, NodeName)  
NodeId would be a Primary Key

2. Connection(SourceNodeID, DestinationNodeID, Strength)  
SourceNodeID and DestinationNodeID would be a Foreign Key Constrains to Node.NodeID