Network management: a data center inventory

A data center's whole infrastructure as tables, from its racks and routers down to the software running on each machine.

What the model shows

On the hardware side, the data center contains routers and racks. Racks hold switches and machines, and each machine has a type. On the network side, routers, switches, machines, and network zones all route to interfaces. Interfaces connect to zones and to each other, and they expose ports. A connection joins two ports. On the software side, machines run three kinds of process: operating system, application, and service. The application and service processes listen on ports. Operating systems, services, and other software each have a chain of versions. Applications depend on services, other software, and specific versions.

It's the larger of the two samples, and a good one for exploring layers: Hardware, Network, and Software each hold one part of the data center, and you can show or hide each part on its own. Some tables have two names, such as Router and Egress, some foreign keys point from a table to itself, such as from one Version to the one before it, and three different foreign keys run from Port to Interface.

Tables and columns

Application_Process

• id: INTEGER – primary key
• name: VARCHAR(100)
• pid: INTEGER
• startTime: INTEGER
• machineId: INTEGER
• portId: INTEGER
• serviceProcessId: INTEGER
• softwareApplicationId: INTEGER

Connection

• id: VARCHAR(64) – primary key
• source: VARCHAR(100)
• target: VARCHAR(100)
• user: VARCHAR(100)
• time: INTEGER
• fromPortId: INTEGER
• toPortId: INTEGER

DataCenter

• id: INTEGER – primary key
• name: VARCHAR(100)
• location: VARCHAR(100)

Interface

• id: INTEGER – primary key
• ip: INET
• routerId: INTEGER
• routerEgressId: INTEGER
• switchId: INTEGER
• machineId: INTEGER
• networkZoneId: INTEGER
• connectsNetworkZoneId: INTEGER
• connectsInterfaceId: INTEGER
• connectionsInterfaceId: INTEGER

Machine

• id: INTEGER – primary key
• name: VARCHAR(100)
• rackId: INTEGER
• typeId: INTEGER
• updateToVersionId: INTEGER
• updateRequestedAt: TIMESTAMPTZ

Network_Zone

• id: INTEGER – primary key
• ip: INET
• size: INTEGER
• zone: INTEGER

Os_Process

• id: INTEGER – primary key
• name: VARCHAR(100)
• startTime: INTEGER
• machineId: INTEGER
• versionId: INTEGER

Port

• id: INTEGER – primary key
• port: INTEGER
• interfaceId: INTEGER
• opensInterfaceId: INTEGER
• listensInterfaceId: INTEGER

Rack

• id: INTEGER – primary key
• name: VARCHAR(100)
• rack: INTEGER
• zone: INTEGER
• dataCenterId: INTEGER

Router

• id: INTEGER – primary key
• name: VARCHAR(100)
• zone: INTEGER
• dataCenterId: INTEGER

Router_Egress

• id: INTEGER – primary key
• name: VARCHAR(100)
• dataCenterId: INTEGER

Service_Process

• id: INTEGER – primary key
• name: VARCHAR(100)
• pid: INTEGER
• startTime: INTEGER
• machineId: INTEGER
• portId: INTEGER
• softwareServiceId: INTEGER
• versionId: INTEGER

Software

• id: INTEGER – primary key
• name: VARCHAR(100)

Software_Application

• id: INTEGER – primary key
• name: VARCHAR(100)
• ports: INTEGER[]
• dependencies: TEXT[]
• versionId: INTEGER
• softwareServiceId: INTEGER
• softwareId: INTEGER

Software_Os

• id: INTEGER – primary key
• name: VARCHAR(100)
• versions: TEXT[]

Software_Service

• id: INTEGER – primary key
• name: VARCHAR(100)
• versions: TEXT[]
• ports: INTEGER[]
• dependencies: TEXT[]
• versionId: INTEGER

Switch

• id: INTEGER – primary key
• ip: INET
• rack: INTEGER
• rackId: INTEGER

Type

• id: INTEGER – primary key
• name: VARCHAR(100)
• type: VARCHAR(100)
• cpu: INTEGER
• ram: INTEGER
• disk: INTEGER

Version

• id: INTEGER – primary key
• name: VARCHAR(100)
• softwareOsId: INTEGER
• softwareServiceId: INTEGER
• softwareId: INTEGER
• previousVersionId: INTEGER

Foreign keys

• Router → DataCenter (CONTAINS)
• Router_Egress → DataCenter (CONTAINS)
• Rack → DataCenter (CONTAINS)
• Switch → Rack (HOLDS)
• Machine → Rack (HOLDS)
• Machine → Type (TYPE)
• Interface → Router (ROUTES)
• Interface → Router_Egress (ROUTES)
• Interface → Switch (ROUTES)
• Interface → Machine (ROUTES)
• Interface → Network_Zone (ROUTES)
• Interface → Network_Zone (CONNECTS)
• Interface → Interface (CONNECTS)
• Interface → Interface (CONNECTIONS)
• Port → Interface (EXPOSES)
• Port → Interface (OPENS)
• Port → Interface (LISTENS)
• Connection → Port (FROM)
• Connection → Port (TO)
• Os_Process → Machine (RUNS)
• Application_Process → Machine (RUNS)
• Service_Process → Machine (RUNS)
• Machine → Version (UPDATE_TO)
• Application_Process → Port (LISTENS)
• Service_Process → Port (LISTENS)
• Application_Process → Service_Process (DEPENDS_ON)
• Os_Process → Version (INSTANCE)
• Application_Process → Software_Application (INSTANCE)
• Service_Process → Software_Service (INSTANCE)
• Service_Process → Version (INSTANCE)
• Version → Software_Os (VERSION)
• Version → Software_Service (VERSION)
• Version → Software (VERSION)
• Version → Version (PREVIOUS)
• Software_Service → Version (DEPENDS_ON)
• Software_Application → Version (DEPENDS_ON)
• Software_Application → Software_Service (DEPENDS_ON)
• Software_Application → Software (DEPENDS_ON)

Layers

• Software: 8 tables
• Network: 4 tables
• Hardware: 7 tables

Design notes

• Egress routers get a table of their own, so a query can go straight to them instead of filtering every router by a column.
• PREVIOUS points from each version to the one before it, so the versions form a chain. The chain tells you which of two versions is older, even when their names would sort in the wrong order.
• UPDATE_TO flags a machine for an upgrade to a newer version. Its updateRequestedAt column records when the upgrade was requested.
• Connection is a link table: FROM and TO point to the two ports it joins. Because it's a table rather than a single foreign key, other tables can point to a connection too.
• Each row in Process is one running instance of a program. INSTANCE points to the software it runs, or to the exact version.

SQL

Here's the PostgreSQL script Maowl exports for this model: one CREATE TABLE statement per table, with its primary key and foreign keys, then the indexes. Choose another database in the Export dialog, such as MySQL, SQL Server, or Snowflake, to get the same tables for it.

CREATE TABLE DataCenter (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(100),
  location VARCHAR(100)
);

CREATE TABLE Type (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100),
  type VARCHAR(100),
  cpu INTEGER,
  ram INTEGER,
  disk INTEGER
);

CREATE TABLE Rack (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(100),
  rack INTEGER,
  zone INTEGER,
  dataCenterId INTEGER,
  CONSTRAINT CONTAINS_3 FOREIGN KEY (dataCenterId) REFERENCES DataCenter (id)
);

CREATE TABLE Switch (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  ip INET,
  rack INTEGER,
  rackId INTEGER,
  CONSTRAINT HOLDS FOREIGN KEY (rackId) REFERENCES Rack (id)
);

CREATE TABLE Router (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(100),
  zone INTEGER,
  dataCenterId INTEGER,
  CONSTRAINT CONTAINS FOREIGN KEY (dataCenterId) REFERENCES DataCenter (id)
);

CREATE TABLE Router_Egress (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(100),
  dataCenterId INTEGER,
  CONSTRAINT CONTAINS_2 FOREIGN KEY (dataCenterId) REFERENCES DataCenter (id)
);

CREATE TABLE Network_Zone (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  ip INET,
  "size" INTEGER,
  zone INTEGER
);

CREATE TABLE Software_Os (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(100),
  versions TEXT[]
);

CREATE TABLE Software (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE Version (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(100),
  softwareOsId INTEGER,
  softwareServiceId INTEGER,
  softwareId INTEGER,
  previousVersionId INTEGER,
  CONSTRAINT VERSION FOREIGN KEY (softwareOsId) REFERENCES Software_Os (id),
  CONSTRAINT VERSION_3 FOREIGN KEY (softwareId) REFERENCES Software (id),
  CONSTRAINT PREVIOUS FOREIGN KEY (previousVersionId) REFERENCES Version (id)
);

CREATE TABLE Machine (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100),
  rackId INTEGER,
  typeId INTEGER,
  updateToVersionId INTEGER,
  updateRequestedAt TIMESTAMPTZ,
  CONSTRAINT HOLDS_2 FOREIGN KEY (rackId) REFERENCES Rack (id),
  CONSTRAINT TYPE FOREIGN KEY (typeId) REFERENCES Type (id),
  CONSTRAINT UPDATE_TO FOREIGN KEY (updateToVersionId) REFERENCES Version (id)
);

CREATE TABLE Interface (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  ip INET,
  routerId INTEGER,
  routerEgressId INTEGER,
  switchId INTEGER,
  machineId INTEGER,
  networkZoneId INTEGER,
  connectsNetworkZoneId INTEGER,
  connectsInterfaceId INTEGER,
  connectionsInterfaceId INTEGER,
  CONSTRAINT ROUTES FOREIGN KEY (routerId) REFERENCES Router (id),
  CONSTRAINT ROUTES_2 FOREIGN KEY (routerEgressId) REFERENCES Router_Egress (id),
  CONSTRAINT ROUTES_3 FOREIGN KEY (switchId) REFERENCES Switch (id),
  CONSTRAINT ROUTES_4 FOREIGN KEY (machineId) REFERENCES Machine (id),
  CONSTRAINT ROUTES_5 FOREIGN KEY (networkZoneId) REFERENCES Network_Zone (id),
  CONSTRAINT CONNECTS FOREIGN KEY (connectsNetworkZoneId) REFERENCES Network_Zone (id),
  CONSTRAINT CONNECTS_2 FOREIGN KEY (connectsInterfaceId) REFERENCES Interface (id),
  CONSTRAINT CONNECTIONS FOREIGN KEY (connectionsInterfaceId) REFERENCES Interface (id)
);

CREATE TABLE Port (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  port INTEGER,
  interfaceId INTEGER,
  opensInterfaceId INTEGER,
  listensInterfaceId INTEGER,
  CONSTRAINT EXPOSES FOREIGN KEY (interfaceId) REFERENCES Interface (id),
  CONSTRAINT OPENS FOREIGN KEY (opensInterfaceId) REFERENCES Interface (id),
  CONSTRAINT LISTENS FOREIGN KEY (listensInterfaceId) REFERENCES Interface (id)
);

CREATE TABLE Connection (
  id VARCHAR(64) PRIMARY KEY,
  source VARCHAR(100),
  target VARCHAR(100),
  "user" VARCHAR(100),
  time INTEGER,
  fromPortId INTEGER,
  toPortId INTEGER,
  CONSTRAINT "FROM" FOREIGN KEY (fromPortId) REFERENCES Port (id),
  CONSTRAINT "TO" FOREIGN KEY (toPortId) REFERENCES Port (id)
);

CREATE TABLE Os_Process (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(100),
  startTime INTEGER,
  machineId INTEGER,
  versionId INTEGER,
  CONSTRAINT RUNS FOREIGN KEY (machineId) REFERENCES Machine (id),
  CONSTRAINT INSTANCE FOREIGN KEY (versionId) REFERENCES Version (id)
);

CREATE TABLE Software_Service (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(100),
  versions TEXT[],
  ports INTEGER[],
  dependencies TEXT[],
  versionId INTEGER,
  CONSTRAINT DEPENDS_ON_2 FOREIGN KEY (versionId) REFERENCES Version (id)
);

CREATE TABLE Service_Process (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(100),
  pid INTEGER,
  startTime INTEGER,
  machineId INTEGER,
  portId INTEGER,
  softwareServiceId INTEGER,
  versionId INTEGER,
  CONSTRAINT RUNS_3 FOREIGN KEY (machineId) REFERENCES Machine (id),
  CONSTRAINT LISTENS_3 FOREIGN KEY (portId) REFERENCES Port (id),
  CONSTRAINT INSTANCE_3 FOREIGN KEY (softwareServiceId) REFERENCES Software_Service (id),
  CONSTRAINT INSTANCE_4 FOREIGN KEY (versionId) REFERENCES Version (id)
);

CREATE TABLE Software_Application (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(100),
  ports INTEGER[],
  dependencies TEXT[],
  versionId INTEGER,
  softwareServiceId INTEGER,
  softwareId INTEGER,
  CONSTRAINT DEPENDS_ON_3 FOREIGN KEY (versionId) REFERENCES Version (id),
  CONSTRAINT DEPENDS_ON_4 FOREIGN KEY (softwareServiceId) REFERENCES Software_Service (id),
  CONSTRAINT DEPENDS_ON_5 FOREIGN KEY (softwareId) REFERENCES Software (id)
);

CREATE TABLE Application_Process (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(100),
  pid INTEGER,
  startTime INTEGER,
  machineId INTEGER,
  portId INTEGER,
  serviceProcessId INTEGER,
  softwareApplicationId INTEGER,
  CONSTRAINT RUNS_2 FOREIGN KEY (machineId) REFERENCES Machine (id),
  CONSTRAINT LISTENS_2 FOREIGN KEY (portId) REFERENCES Port (id),
  CONSTRAINT DEPENDS_ON FOREIGN KEY (serviceProcessId) REFERENCES Service_Process (id),
  CONSTRAINT INSTANCE_2 FOREIGN KEY (softwareApplicationId) REFERENCES Software_Application (id)
);

ALTER TABLE Version ADD CONSTRAINT VERSION_2 FOREIGN KEY (softwareServiceId) REFERENCES Software_Service (id);

CREATE INDEX idx_software_name ON Software (name);
CREATE INDEX idx_machine_name ON Machine (name);
CREATE UNIQUE INDEX idx_interface_ip ON Interface (ip);
CREATE INDEX idx_connection_time ON Connection (time);

Source

The model is based on neo4j-graph-examples/network-management on GitHub. Download the model as network-management.json.