New database schema

Im trying to make a database that can result in something like


So it looks like ill need 6 tables;
power_panels
receptacles
pdus
upss
racks
devices
and relate them like

create table power_panels (
   power_panel_id SMALLINT NOT NULL AUTO_INCREMENT,
   power_panel_circuit VARCHAR(100),
   power_panel_amps INT,
   power_panel_poles VARCHAR(10),
   power_panel_created_by VARCHAR(25),
   power_panel_created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   power_panel_updated_by VARCHAR(25) NULL,
   power_panel_updated_date TIMESTAMP NULL,
   power_panel_enabled CHAR(1) DEFAULT '1',
   PRIMARY KEY ( power_panel_id )
);

create table recepticles (
   recepticle_id INT NOT NULL AUTO_INCREMENT,
   power_panel_id SMALLINT,
   recepticle_type TINYINT,
   recepticle_junction_box_size TINYINT,
   recepticle_conduit_type TINYINT,
   recepticle_conduit_length SMALLINT,
   recepticle_created_by VARCHAR(25),
   recepticle_created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   recepticle_updated_by VARCHAR(25) NULL,
   recepticle_updated_date TIMESTAMP NULL,
   recepticle_enabled CHAR(1) DEFAULT '1',
   FOREIGN KEY ( power_panel_id ) REFERENCES power_panels (power_panel_id),   
   PRIMARY KEY ( recepticle_id )
);

create table pdus (
   pdu_id SMALLINT NOT NULL AUTO_INCREMENT,
   recepticle_id INT,
   ups_pdu_id SMALLINT NULL,
   pdu_number_of_outlets TINYINT,
   pdu_created_by VARCHAR(25),
   pdu_created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   pdu_updated_by VARCHAR(25) NULL,
   pdu_updated_date TIMESTAMP NULL,
   pdu_enabled CHAR(1) DEFAULT '1',
   FOREIGN KEY ( recepticle_id ) REFERENCES recepticles (recepticle_id),   
   PRIMARY KEY ( pdu_id )
);

create table upss (
   ups_id SMALLINT NOT NULL AUTO_INCREMENT,
   recepticle_id INT,
   ups_pdu_id SMALLINT NULL,
   ups_number_of_outlets TINYINT,
   ups_created_by VARCHAR(25),
   ups_created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   ups_updated_by VARCHAR(25) NULL,
   ups_updated_date TIMESTAMP NULL,
   ups_enabled CHAR(1) DEFAULT '1',
   FOREIGN KEY ( recepticle_id ) REFERENCES recepticles (recepticle_id),   
   PRIMARY KEY ( ups_id )
);

create table racks (
   rack_id INT NOT NULL AUTO_INCREMENT,
   rack_title VARCHAR(25),
   rack_slots TINYINT DEFAULT 45,
   rack_created_by VARCHAR(25),
   rack_created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   rack_updated_by VARCHAR(25) NULL,
   rack_updated_date TIMESTAMP NULL,
   rack_enabled CHAR(1) DEFAULT '1',
   PRIMARY KEY ( rack_id )
);

create table devices (
   device_id INT NOT NULL AUTO_INCREMENT,
   pdu_id SMALLINT NULL,
   ups_id SMALLINT NULL,
   rack_id INT NOT NULL,
   device_orientation CHAR(1) DEFAULT '1',
   device_beginning_slot DECIMAL(3,1) NOT NULL,
   device_ending_slot DECIMAL(3,1) NOT NULL,
   device_title VARCHAR(100) NOT NULL,
   device_notes TEXT DEFAULT NULL,
   device_width INT DEFAULT NULL,
   device_beginning_x INT DEFAULT NULL,
   device_power_requirement DECIMAL(6,2) DEFAULT 16.9,
   device_created_by VARCHAR(25),
   device_created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   device_enabled CHAR(1) DEFAULT '1',
   device_updated_by VARCHAR(25) NULL,
   device_updated_date TIMESTAMP NULL,
   FOREIGN KEY ( pdu_id ) REFERENCES pdus (pdu_id),   
   FOREIGN KEY ( ups_id ) REFERENCES upss (pdu_id),   
   PRIMARY KEY ( device_id )
);

But since the devices (white boxes within racks) can either connect to a PDU or a UPS, I was confused as to their relationship or even if I needed a pdu and a ups table to keep them separate

What is the correct way?

i think you’re looking for a supertype (power source?) with two subtypes (pdu or ups)

google subtype and supertype tables

2 Likes

i think you’re looking for a supertype (power source?) with two subtypes (pdu or ups)
u can google it or follow the book database modeling by TOBY J.

1 Like