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?