1

Data matrix from excel

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!

2 Answers 2

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.