Table choosing algorithm

Question

Below I'm using simple example, real tables are bigger, I cannot store devices in one table, I can not merge device1 and device2 tables.

I have three tables: device1, device2, computer(device3), device1 and device2 have id from the same sequence, device3 is computer. Table device3 can be connected with device1 and device2 (many-to-many), so I have two tables with relations: rel1with3 and rel2with3.

CREATE SEQUENCE device_1or2;
CREATE SEQUENCE device_3;

CREATE TABLE device1 
  ( d_id NUMBER, d_data VARCHAR2(20)
  , CONSTRAINT device1_pk PRIMARY KEY (d_id));
CREATE TABLE device2 
  ( d_id NUMBER, d_data VARCHAR2(20)
  , CONSTRAINT device2_pk PRIMARY KEY (d_id));
CREATE TABLE computer
  ( d_id NUMBER, d_data VARCHAR2(20)
  , CONSTRAINT device3_pk PRIMARY KEY (d_id));

CREATE TABLE rel1with3 
  ( d_id_1 NUMBER, d_id_3 NUMBER
  , CONSTRAINT rel13_fk_1 FOREIGN KEY (d_id_1) REFERENCES device1 (d_id)
  , CONSTRAINT rel13_fk_3 FOREIGN KEY (d_id_3) REFERENCES computer (d_id)
);
CREATE UNIQUE INDEX I_REL_13 ON rel1with3 (d_id_1, d_id_3);

CREATE TABLE rel2with3 
( d_id_2 NUMBER, d_id_3 NUMBER
, CONSTRAINT rel23_fk_2 FOREIGN KEY (d_id_2) REFERENCES device2 (d_id)
, CONSTRAINT rel23_fk_3 FOREIGN KEY (d_id_3) REFERENCES computer (d_id)
);
CREATE UNIQUE INDEX I_REL_23 ON rel2with3 (d_id_2, d_id_3);

I want to create stored procedure which have two in parameters (device1 or device2 as first parameter, computer as second) and add/remove relation (insert/delete row in relation table).

CREATE OR REPLACE PROCEDURE Add_Relation
( COMPUTER NUMBER
, DEVICE   NUMBER
, ERR      OUT NUMBER
) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    device_data VARCHAR2(20);
BEGIN
  -- detect which device it is, insert record
  -- stored procedure, the same for both device type, BUT uses device data from additional table field
  WRITE_LOG(device, device_data, computer);
  COMMIT;
  EXCEPTION
    WHEN OTHERS THEN ERR := TO_CHAR(SQLCODE);
END;

Question is: HOW better organize this? Using another function? Using IF-ELSE? How is faster for execute etc?

Answer 1

I would just use one sequence object and have one device table. In the device table, I'd have it like this:

CREATE TABLE device
(
   ID NUMBER NOT NULL PRIMARY KEY
 , device_name VARCHAR2(100) NOT NULL
 , device_type VARCHAR2(100) NOT NULL
 , parent_device_id NUMBER NULL
 , child_device_id NUMBER NULL
);

alter table device
  add constraint FK1 foreign key (parent_device_id)
  references device (ID);

alter table device
  add constraint FK2 foreign key (child_device_id)
  references device (ID);

For devices of type 3 (many-to-many) I you would have values for parent_device_id and child_device_id (these would be the devices that device 3 is connected to). If there aren't any devices related to this device, then these columns would be null for the given device.

If there is a better name than parent/child, you can use whatever is proper. The idea is that you just store the devices in one table, and map the relationships accordingly.

parent_device_id and child_device_id would have foreign keys back to the device table on the id column, so that would ensure you never "orphan" any device records.

You can use the same sequence object to generate new id's as you insert device records.

Answer 2

I would simply try to insert in rel1with3, if it fails try with rel2with3:

CREATE OR REPLACE PROCEDURE Add_Relation(computer NUMBER, device NUMBER) IS
   device_data VARCHAR2(20);
   parent_key_not_found EXCEPTION;
   PRAGMA EXCEPTION_INIT(parent_key_not_found, -2291);
BEGIN
   BEGIN
      INSERT INTO rel1with3 (d_id_1, d_id_3) VALUES (computer, device);
      device_data := 'device1';
   EXCEPTION
      WHEN parent_key_not_found THEN
         INSERT INTO rel2with3 (d_id_2, d_id_3) VALUES (computer, device);
         device_data := 'device2';
   END;
   WRITE_LOG(device, device_data, computer);
END;

A few suggestions:

1. use PL/SQL exceptions instead of return out parameters (you can easily forget to check out parameters, leading to hard to diagnose bugs)
2. don't use an autonomous transaction unless you have a need to use them (in this case they will break your transaction flow).
3. use transaction processes (commit/rollback) only in the top calling procedure/code (usually the calling app, not PL/SQL)
4. don't forget to index both referencing columns on rel1with3 and rel2with3 (usually you will have a PK on (d_id_1, d_id_3) so you will need an additional index on d_id_3)