2

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.

  • 1
    You only use the word "device", but it seems you have the category of peripheral and non-peripheral. That is to say, can a storage device be connected to another storage device exclusively. OR does everything have to connect to a server. – Evan Carroll Oct 17 '17 at 3:00
  • Excuse me, in the diagram I don't it explicit but as I said all devices could link with the others. So, a switch could link with other switch or store unit, etc. @Evan – BiteBat Oct 18 '17 at 18:53
1

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.

  • I like your solution, but here I ask me if better have a model with relations between concepts or in your case (as I understood), create two isolate concepts and binding the data by query. Is clear that the integrity restrictions in your solution will be in the above layer. @Evan – BiteBat Oct 18 '17 at 18:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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