Designing a Multiple Reflexive Relationship

database-designerd

I'm making a database diagram and I'm facing the situation explained below.

Business rules:

  • A device could have multiple connections with others devices (It's a graph).
  • A device could be a server, a switch or store unit (for the moment, could raise).
  • A device belong a one system.

So, I need it design this multiple reflexive relationship between devices. I believe that I achieved it with the next model, but I'm not sure if it's the best solution.

Reality and Problem:

enter image description here

A device could have multiple devices (links) and belong to multiple devices.

So my diagram is as follows:

enter image description here

Diagram Explanation:

The Device concept was created because the information of all devices is similar relative. So the properties that differ could be optional. I believe that the solution for the multiple reflexive relationship is to create other concept that has a copy of first concept ID (device) to apply the partition and get the link between multiple devices. This copy is generated when some inserts a device (with a trigger or with simple other insert, in chain from logic layer). The purpose of the ID copy is to allow creating relationships between all devices.

Any idea for this case? Thanks.

Best Answer

I'll answer for an RDBMS, like PostgreSQL. Here we use a many-to-many table.

CREATE TYPE device_type
  AS ENUM ( 'SERVER', 'STORAGE', 'SWITCH' );

CREATE TABLE devices (
  id   serial         PRIMARY KEY,
  type device_type
);

CREATE TABLE device_map (
  id_device   int REFERENCES devices,
  id_parent   int REFERENCES devices,
  PRIMARY KEY (id_device, id_parent)
);

INSERT INTO devices(id,type)
VALUES
  (1,'SERVER'),(2,'SERVER'),
  (3,'STORAGE'),(4,'STORAGE'),(5,'STORAGE'),
  (6,'SWITCH');

-- Everything the first and second server is connected to.
INSERT INTO device_map(id_device,id_parent)
VALUES
  (1,2),(1,3),(1,6),
  (2,1),(2,3),(2,6),(2,4),(2,5);

Now I think you want something like this.

SELECT id_device, d1.type AS device, id_parent, d2.type AS parent
FROM device_map JOIN devices AS d1 ON id_device = d1.id
JOIN devices AS d2 ON id_parent = d2.id
ORDER BY id_device, d2.type = 'SERVER' DESC;
 id_device | device | id_parent | parent  
-----------+--------+-----------+---------
         1 | SERVER |         2 | SERVER
         1 | SERVER |         3 | STORAGE
         1 | SERVER |         6 | SWITCH
         2 | SERVER |         1 | SERVER
         2 | SERVER |         3 | STORAGE
         2 | SERVER |         6 | SWITCH
         2 | SERVER |         4 | STORAGE
         2 | SERVER |         5 | STORAGE
(8 rows)

That models this schema, assuming simply that non-servers can only be connected to servers.

Related Question