Mysql – Designing an auto-related table

database-designMySQL

I'm a newbie in database design but I need to design one.
I need to have a record of the devices connected to a network in which certain device could be connected to many devices or any.

In the device's table I've got the following columns:

Device ID(char 25), 
IP address (char 25), 
SN (PRIMARY KEY char 20)

All that information is taken from two CISCO commands (CDP show neighbors detail and show inventory) the info is placed in two text files.

The first file has the information of the device I'm connecting to via telnet and looks like this: 

SN: FDO1129Z9ZJ
Device ID: Barragan_3750
IP: 148.227.2.158

The second file contains information about devices connected to this device, something like this:

Device ID: BIOTERIO
IP address: 148.228.83.189
Interface: GigabitEthernet1/0/6
Port ID (outgoing port): GigabitEthernet0/1
SN: P7K08UQ         

Device ID: LAB_PESADO
IP address: 148.228.130.158
Interface: GigabitEthernet1/0/11
Port ID (outgoing port): GigabitEthernet0/1
SN: FNS174202FT     

Device ID: Arquitectura_Salones
IP address: 148.228.1420.11
Interface: GigabitEthernet1/0/9
Port ID (outgoing port): GigabitEthernet0/49
SN: FNS14420420

I'm not sure how to represent the relationship because Interface (char 25) and Port ID (char 25) should be relationship's attributes.

Could you help me?

Best Answer

If I were you I would break this into a minimum of three tables:

  1. Devices
  2. Ports
  3. Interfaces

You have already got most of the data you will need, I do suggest also considering the MAC address of different ports where available.

If I were building these out, I would use the following fields:

    CREATE TABLE device {
    serial_num VARCHAR(25) PRIMARY KEY,
    device_id VARCHAR(255) UNIQUE KEY,
    ip_add BINARY(16)
};

CREATE TABLE ports {
    device_serial VARCHAR(25) NOT NULL,
    port_id INT NOT NULL,
    port_type varchar(40),
    port_num varchar(15),
    mac_add BINARY(48),
    PRIMARY KEY (device_serial, port_id),
    UNIQUE KEY (mac_add)
};

CREATE TABLE interfaces {
    interface_id INT NOT NULL PRIMARY KEY,
    interface_name VARCHAR(255),
    port_a INT,
    port_b INT,
    ip_add BINARY(16),
    UNIQUE KEY (port_a, port_b)
};

This would allow you to specify most of the fields with a degree of specificity.

Each "device" could either be a router or a switch, or even an end user device, as far as the network is concerned it's simply something which can be connected. It might benefit you to add an ENUM type to let you easily assign a status to each device as well, however for now you can keep it simple.

Since most ports will have a MAC address, and MAC addresses are globally unique, it would make sense to give each MAC address a unique key, allowing it to be nullable allows you to use serial ports and the like as well which don't have MAC addresses afaik. The point of the composite Primary key in the ports table is to tie each port to a device, that way ports are not allowed to float around on theIr own confusing the net admins.

The Interfaces table is how things tie together, an interface is simplY a connection between two ports. I gave the interface the IP address, as monitoring of a device over TCP/IP tends to start from seeing if the IP address is responding and it makes the most sense, it also helps to make sure only one pair of ports are using an IP address at a time.

A few notes on this design, you will need to use some scripting language to make full use of this, I determined to use a binary datatype for IP and MAC addresses based on a useful answer on our sister site. Likewise, converting a MAC Address back and forth can be done with this PHP function.

There are certainly improvements which could be made to this design, however I think it serves as a good boilerplate for a simple network management DB.

Related Question