Search code examples
sqldatabase-designdatabase-schema

Database schema design for network devices and configuration

I have the following database design problem:

Given the four entity types: Interface, Device, VLAN, VNI.

There are the following rules:

  1. An Interface has exactly one Device. Consequently, a Device may have zero to many Interfaces.
  2. An Interface can have zero to many VLANs. A VLAN can have zero to many Interfaces. A VLAN cannot be assigned to the same Interface multiple times.
  3. A VLAN can have zero or one VNI. Consequently a VNI has zero to many VLANs.

So far this is easy, it can be modelled like this:

Interface(id, device_id not null)

Device(id)

VLAN(id, vni_id nullable)

VNI(id)

InterfaceVLAN(interface_id not null, vlan_id not null) unique (interface_id, vlan_id)

But there is a 4th rule: The tuple (VNI, Device, VLAN) must be unique.

It must also be possible to assign a VLAN to a VNI without that VLAN being assigned to an Interface or a Device. Also, a VLAN can be assigned to an Interface without the VLAN having a VNI.

I am stuck on how to incorporate this into the model and would welcome any suggestions.

Solution

  • From the question:

    The tuple (VNI, Device, VLAN) must be unique.

    From your comment to James Hu:

    Each VNI should map to no more than one VLAN on each Device.

    These are two different constraints. The second one means that (Device, VNI) is unique, and it implies the first one, but not the other way around.

    -- Vni tag VNI exists.
--
vni_tag {VNI}
     PK {VNI}


-- Virtual lan (vlan) VLN exists.
--
vlan {VLN}
  PK {VLN}
    • Each vlan is mapped to at most one vni tag; for each vni tag, that tag may be mapped to more than one vlan.
    -- Vlan VLN is mapped to vni tag VNI.
--
vln_vni {VLN, VNI}
     PK {VLN}
     SK {VLN, VNI}

FK1 {VLN} REFERENCES vlan    {VLN}
FK2 {VNI} REFERENCES vni_tag {VNI}
    • Each device may have more than one interface;
      for each interface: that interface belongs to exactly one device.
    -- Device DEV exists.
--
device {DEV}
    PK {DEV}


-- Interface number IFC# of device DEV exists.
--
interface {DEV, IFC#}
       PK {DEV, IFC#}

FK {DEV} REFERENCES device {DEV}

    Unique (DEV, VNI)

    In the case of the second constraint, (DEV, VNI) unique:

    • Each interface may be connected to more than one vlan; for each vlan, more than one interface may be connected to that vlan.

    • For each combination of device and vni tag, that combination of the device and the tag may occur at most once.

    -- Interface number IFC# of device DEV is connected to
-- vlan VLN, which is mapped to vni tag VNI.
--
ifc_vln_vni {DEV, IFC#, VLN, VNI}
         PK {DEV, IFC#, VLN}
         AK {DEV, VNI}

FK1 {DEV, IFC#} REFERENCES interface {DEV, IFC#}
FK2 {VLN, VNI}  REFERENCES vln_vni   {VLN, VNI}

    Unique (DEV, VLN, VNI)

    In the case of the first constraint, (DEV, VLN, VNI) unique:

    • Each interface may be connected to more than one vlan; for each vlan, more than one interface may be connected to that vlan.

    • For each combination of device, vlan, and vni tag, that combination of the device, the vlan, and the tag may occur at most once.

    -- Interface number IFC# of device DEV is connected to
-- vlan VLN, which is mapped to vni tag VNI.
--
ifc_vln_vni {DEV, IFC#, VLN, VNI}
         PK {DEV, IFC#, VLN}
         AK {DEV, VLN,  VNI}

FK1 {DEV, IFC#} REFERENCES interface {DEV, IFC#}
FK2 {VLN, VNI}  REFERENCES vln_vni   {VLN, VNI}

    Note:

    All attributes (columns) NOT NULL

PK = Primary Key
SK = Proper Superkey (Unique)
AK = Alternate Key   (Unique)
FK = Foreign Key

    Using suffix # to save on screen space.
OK for SQL Server and Oracle, for others use _NO.
For example, rename IFC# to IFC_NO.