Alternative many-to-many relationship implementation in Oracle

The benefit of nested tables, or, as they are also called, child tables ( NESTED TABLE) is rather doubtful for two reasons: it is impossible to create foreign keys ( FOREIGN KEY) for types and objects , and it is very simple to implement functionality using an additional table. And it would be possible to find other advantages for nested tables, if not for the fact that after creating the main table, the column of the nested table can no longer be changed. You can, of course, create a second type and use it, but this is a fairly large amount of work and, undoubtedly, a lot of concern about existing data.

However, I had the idea of ​​implementing a many-to-many relationship (MANY-TO-MANY RELATIONSHIP) with the help of a nested table, because the idea is so tempting - instead of storing one foreign key, store their set for a specific record - what could be more logical from the point of view of human logic? But the question arose - is the game worth the candle?

Example

It’s easier to show the proposed structure using an example, and it’s also a good way to check which method loads the system less.

Suppose there is a bus fleet spherical in a vacuum. The bus fleet has several buses and several drivers. Several drivers ride on the same bus (several shifts). The features of this fleet are such that one driver can also ride on several buses, for example, depending on the day of the week.

Let's create two very simple tables, designed only to create relationships between them.

CREATE TABLE tab_bus
 ( b_id NUMBER PRIMARY KEY
 , bus_number VARCHAR2(9) NOT NULL
 );
CREATE SEQUENCE seq_bus;

CREATE TABLE tab_driver
 ( d_id NUMBER PRIMARY KEY
 , driver_name VARCHAR2(255) NOT NULL
 );
CREATE SEQUENCE seq_driver;

* This source code was highlighted with Source Code Highlighter.

To begin, we implement the relationship using the most common approach: create a table of relationships.

CREATE TABLE bus_driver
 ( bus_id NUMBER
 , driver_id NUMBER
 , CONSTRAINT pk_driver_bus PRIMARY KEY (bus_id, driver_id)
 , CONSTRAINT fk_bus_id FOREIGN KEY (bus_id) REFERENCES tab_bus (b_id)
 , CONSTRAINT fk_driver_id FOREIGN KEY (driver_id) REFERENCES tab_driver (d_id)
 );

* This source code was highlighted with Source Code Highlighter.

The primary key ( PRIMARY KEY) on two fields guarantees the fulfillment of all necessary conditions: the content of the fields (each separately) cannot be empty and each relationship will be unique. In addition, this approach allows you to get rid of additional fields and provides automatic indexing.

To create, delete and select relationships in the example, the procedures will be used - for the convenience of testing. In addition, I was interested in testing with procedures, because from the point of view of programming it is more convenient than compiling queries in the body of the program. It should be noted that there is no commit in one procedure (COMMIT;) Firstly, when I test it, I do it manually, and secondly, the procedures for adding and removing relationships start in a loop during testing - in this case, the commit will only interfere.

CREATE PROCEDURE add_relation
 ( p_bus NUMBER
 , p_driver NUMBER
 ) AS
BEGIN
 INSERT INTO bus_driver VALUES ( p_bus, p_driver );
END;

CREATE PROCEDURE drop_relation
 ( p_bus NUMBER
 , p_driver NUMBER
 ) AS
BEGIN
 DELETE bus_driver WHERE bus_id = p_bus AND driver_id = p_driver;
END;

CREATE PROCEDURE select_relation
 ( p_data OUT SYS_REFCURSOR
 ) AS
BEGIN
 OPEN p_data FOR
  SELECT b.bus_number, d.driver_name
   FROM tab_bus b, tab_driver d, bus_driver r
   WHERE (b.b_id = r.bus_id) AND (r.driver_id = d.d_id);
END;

* This source code was highlighted with Source Code Highlighter.

It is probably worth noting that here in the sampling procedure the JOINconstruction is used as the join of the tables ( 'a) WHERE. There are two reasons for this: the Oracle doesn’t care, he considers it JOIN'oh, and it’s more convenient for me to compare the complexity of constructing SQL query queries.

This completes the creation of the first relationship structure. The following relationship scheme was implemented: two tables and an additional table in which a list of relationships is stored.

Next comes the SQL code for an alternative. The second option was created on exactly the same table structure, but in a different scheme. And the relationship structure looks like this: there are two tables, in one of them each record stores a list of relationships.

A table can be created immediately with a column of nested tables, but in this case, the relationship is constructed from ready-made two tables, so a different approach is used: one of the tables is changed.

CREATE OR REPLACE TYPE obj_list AS OBJECT
 ( r_driver NUMBER
 );
CREATE OR REPLACE TYPE nt_list AS TABLE OF obj_list;
ALTER TABLE tab_bus ADD
 ( bus_drivers nt_list NULL
 ) NESTED TABLE bus_drivers STORE AS nt_bus_drivers;

* This source code was highlighted with Source Code Highlighter.

As already mentioned, foreign keys cannot be created in nested tables, so it makes sense to write your own bike, which will undoubtedly negatively affect the speed of work with relationships.

CREATE FUNCTION check_fk
 ( p_id NUMBER
 ) RETURN NUMBER IS
 fk_count NUMBER;
BEGIN
 SELECT COUNT(d_id) INTO fk_count
  FROM tab_driver WHERE d_id = p_id;
 RETURN fk_count;
END;

* This source code was highlighted with Source Code Highlighter.

And finally, create, delete, and select procedures for relationships. It is worth noting the exception blocks at the end of the procedures. The fact is that the first value for the record must be added using UPDATE, i.e. actually change the entire recording cell. But subsequent relationships are added with the help of the command INSERT. The procedure assumes that there is already a record, and in the event of an exception, it acts as if the record has no connections. Checking before trying to insert the relationship does not make sense, since this is a lot of action. If you need to handle other exceptions and plan to write different code for the general case, then you should clarify the processing of the exception, add the exception number to the variables and already perform an action on it.

CREATE PROCEDURE add_relation
 ( p_bus  NUMBER
 , p_driver NUMBER
 ) AS
BEGIN
 IF (check_fk(p_driver) = 1) THEN
  INSERT INTO TABLE
   ( SELECT bus_drivers
    FROM tab_bus
    WHERE b_id = p_bus
   )
  VALUES ( obj_list(p_driver) );
 ELSE
  RAISE_APPLICATION_ERROR(-20665, 'Record doesn''t exist.');
 END IF;
 DBMS_OUTPUT.PUT_LINE( TO_CHAR( (DBMS_UTILITY.GET_TIME-start_time)/100, '09.99' ) );
 EXCEPTION
  WHEN OTHERS THEN
   UPDATE tab_bus
    SET bus_drivers = nt_list ( obj_list(p_driver) )
    WHERE b_id = p_bus;
END add_relation;

CREATE PROCEDURE drop_relation
 ( p_bus  NUMBER
 , p_driver NUMBER
 ) AS
BEGIN
 IF (check_fk(p_driver) = 1) THEN
  DELETE TABLE
   ( SELECT bus_drivers
    FROM tab_bus d
    WHERE d.b_id = p_bus
   ) nt
   WHERE nt.r_driver = p_bus;
  END IF;
 EXCEPTION
  WHEN OTHERS THEN
   NULL;
END drop_relation;

CREATE PROCEDURE select_relation
 ( p_data  OUT SYS_REFCURSOR
 ) AS
BEGIN
 OPEN p_data FOR
  SELECT b.bus_number, d.driver_name
   FROM tab_bus b, tab_driver d, TABLE (b.bus_drivers) r
   WHERE (b.b_id = p_bus) AND (d.d_id = p_driver);
END;

* This source code was highlighted with Source Code Highlighter.

The deletion procedure also has exception handling, as it happens if the table is empty. In the case of an exception, you don’t need to do anything, since the record you are looking for does not exist anymore, which is necessary for the deletion procedure.
To verify the correct operation of the procedures, the same test values ​​were used.

INSERT INTO tab_driver VALUES ( seq_driver.NEXTVAL, 'Viktor Jeliseev');
INSERT INTO tab_driver VALUES ( seq_driver.NEXTVAL, 'Stepan Kljavin');
INSERT INTO tab_driver VALUES ( seq_driver.NEXTVAL, 'Marija Baranka');
INSERT INTO tab_driver VALUES ( seq_driver.NEXTVAL, 'Arsenij Dubov');
INSERT INTO tab_bus VALUES ( seq_bus.NEXTVAL, 'p666pp');
INSERT INTO tab_bus VALUES ( seq_bus.NEXTVAL, 'LT-3216');
INSERT INTO tab_bus VALUES ( seq_bus.NEXTVAL, 'zox-15');
INSERT INTO tab_bus VALUES ( seq_bus.NEXTVAL, 'x234oo');

BEGIN
 add_relation (1, 3);
 add_relation (1, 4);
 add_relation (3, 3);
 add_relation (3, 2);
 add_relation (2, 2);
 drop_relation (3, 2);
 drop_relation (2, 2);
END;

DECLARE
 g_data SYS_REFCURSOR;
BEGIN
 select_relation (1, 4, g_data);
 select_relation (3, 3, g_data);
 select_relation (2, 2, g_data);
 select_relation (1, 1, g_data);
END;

* This source code was highlighted with Source Code Highlighter.

Testing

Testing was carried out several times for each option. At first, the selections, procedures, and function were debugged on the above test data. Then, procedures were created for each database (actually, of course, schemas), which populated tables with data using a random number generator. In approximately the same way, a predetermined number of relationships was created.

CREATE OR REPLACE
PROCEDURE random_insert_data
 ( record_count NUMBER
 ) AS
 start_time NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
 counter NUMBER;
 field_value VARCHAR2(255);
BEGIN
 FOR counter IN 1..record_count
 LOOP
  field_value := DBMS_RANDOM.STRING('A', 9);
  INSERT INTO tab_bus (b_id, bus_number) VALUES
   ( seq_bus.NEXTVAL
   , field_value
   );
 END LOOP;
 FOR counter IN 1..record_count
 LOOP
  field_value := INITCAP(DBMS_RANDOM.STRING('L', 6))||' '||INITCAP(DBMS_RANDOM.STRING('L', 9));
  INSERT INTO tab_driver VALUES
   ( seq_driver.NEXTVAL
   , field_value);
 END LOOP;
 DBMS_OUTPUT.PUT_LINE( TO_CHAR( (DBMS_UTILITY.GET_TIME-start_time)/100, '09.99' ) );
END;

-- for standart many-to-many relations
CREATE PROCEDURE random_insert_rel
 ( rel_count NUMBER
 , rel_from NUMBER
 , rel_to  NUMBER
 ) AS
 start_time NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
 rel1_value VARCHAR2(255);
 rel2_value VARCHAR2(255);
 counter  NUMBER;
BEGIN
 FOR counter IN 1..rel_count
 LOOP
  rel1_value := ROUND(DBMS_RANDOM.VALUE(rel_from, rel_to));
  rel2_value := ROUND(DBMS_RANDOM.VALUE(rel_from, rel_to));
  add_relation(rel1_value, rel2_value);
 END LOOP;
 DBMS_OUTPUT.PUT_LINE( TO_CHAR( (DBMS_UTILITY.GET_TIME-start_time)/100, '09.99' ) );
END;

-- for standart many-to-many relations
CREATE PROCEDURE random_delete_rel
 ( rel_count NUMBER
 , rel_from NUMBER
 , rel_to  NUMBER
 ) AS
 start_time NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
 rel1_value VARCHAR2(255);
 rel2_value VARCHAR2(255);
 counter  NUMBER;
BEGIN
 FOR counter IN 1..rel_count
 LOOP
  rel1_value := ROUND(DBMS_RANDOM.VALUE(rel_from, rel_to));
  rel2_value := ROUND(DBMS_RANDOM.VALUE(rel_from, rel_to));
  drop_relation(rel1_value, rel2_value);
 END LOOP;
 DBMS_OUTPUT.PUT_LINE( TO_CHAR( (DBMS_UTILITY.GET_TIME-start_time)/100, '09.99' ) );
END;

-- for alternative many-to-many system with nested table
CREATE PROCEDURE random_insert_rel
 ( rel_count NUMBER
 , rel_from NUMBER
 , rel_to  NUMBER
 ) AS
 start_time NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
 rel1_value VARCHAR2(255);
 rel2_value VARCHAR2(255);
 counter  NUMBER;
BEGIN
 FOR counter IN 1..rel_count
 LOOP
  rel1_value := ROUND(DBMS_RANDOM.VALUE(rel_from, rel_to));
  rel2_value := ROUND(DBMS_RANDOM.VALUE(rel_from, rel_to));
  add_relation(rel1_value, rel2_value);
 END LOOP;
 DBMS_OUTPUT.PUT_LINE( TO_CHAR( (DBMS_UTILITY.GET_TIME-start_time)/100, '09.99' ) );
END;

-- for alternative many-to-many system with nested table
CREATE PROCEDURE random_delete_rel
 ( rel_count NUMBER
 , rel_from NUMBER
 , rel_to  NUMBER
 ) AS
 start_time NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
 rel1_value VARCHAR2(255);
 rel2_value VARCHAR2(255);
 counter  NUMBER;
BEGIN
 FOR counter IN 1..rel_count
 LOOP
  rel1_value := ROUND(DBMS_RANDOM.VALUE(rel_from, rel_to));
  rel2_value := ROUND(DBMS_RANDOM.VALUE(rel_from, rel_to));
  drop_relation(rel1_value, rel2_value);
 END LOOP;
 DBMS_OUTPUT.PUT_LINE( TO_CHAR( (DBMS_UTILITY.GET_TIME-start_time)/100, '09.99' ) );
END;

* This source code was highlighted with Source Code Highlighter.

Please note that there is no commit in testing procedures either.

results

Testing was carried out on data in the amount of n = 4, 1000, 100000. It is implied that in each table there are n records, and n relationships are created . Testing was conducted on 10 positions, below in the table are 6 of them, which have the most significant time differences and display the most significant data.

During testing, a distinction was made between the first and subsequent samples, as well as between the selection using a query or using a procedure that puts data in the cursor. The main measurements were made to create relationships and different sample options. Removing relationships is not included in this list, since a random number generator is used and it is likely that almost all attempts to remove relationships will result in inaction.

way recording	sample 1	sample 2	sample 3	sample 4	sample 5	creature
add. table 4	00.844	00.792	00.967	01.01	02.02	00.032
in. table 4	00.694	00.707	00.026	00.00	00.00	00.034
add. table, 1000	00.642	00.645	00.698	02.02	02.02	00.142
in. table, 1000	00.687	00.695	00.344	00.00	00.00	00.721
add. table, 100000	00.648	00.697	01.323	00.14	00.49	14.613
in. table, 100000	00.741	00.829	11/01/7	00.00	00.00	84.630

Time is in seconds.

Sample 1: fetch a single relationship from an anonymous block to the cursor, using related variables.
Sample 2: sample one relationship by a procedure using related variables.
Sample 3: Selects relationships from an anonymous block to the cursor.
Sample 4: The first selection of all relationships by the procedure in the cursor.
Sample 5: Subsequent samples of all relationships by the procedure to the cursor.
Add: time to add n relationships.

I would like to say that if we are talking about 1-2 seconds, then time is not important at all, but it should be borne in mind that it is assumed that either the real database will be large or the server will be weaker. The fact that the server on which both options were tested is quite powerful also has its disadvantage: side operations take up too much time, which makes it difficult to make comparisons on a small amount of data.

So, adding an interconnection in the alternative embodiment takes much longer (6 times) than in the standard one, which definitely indicates that the alternative option is not suitable for cases when you need to create and destroy many (more than 500) connections. If there are fewer such actions, then from a human point of view, the difference will be simply imperceptible, despite unnecessary actions, such as replacing the foreign key check.

Now about the sample. Fetching from an anonymous block takes just over half a second in all cases. Probably the fact is that most of the time is spent working with the cursor itself. But when sampling a large amount of data (for example, all), the nested table becomes more efficient: almost less time passes and the only option when the alternative is slower is the first selection of all data.
And finally, the last comparison: query comparison. Personally, I do not see any noteworthy differences.

SELECT b.bus_number, d.driver_name FROM tab_bus b, tab_driver d,  bus_driver r WHERE (b.b_id = r.bus_id)  AND (r.driver_id = d.d_id);

SELECT b.bus_number, d.driver_name FROM tab_bus b, tab_driver d,  TABLE (b.bus_drivers) r WHERE (b.b_id = p_bus)  AND (d.d_id = p_driver);

conclusions

In general, the idea of ​​creating many-to-many relationships using nested tables was not as hopeless as it seemed at first. Despite the fact that the operations of creating and deleting relationships take an order of magnitude longer, the selection of even large queries becomes faster, and the query structure itself is approximately the same in complexity. By the complexity of the sample code, both approaches are equally simple, but to create or remove a relationship, it is more difficult for an alternative approach.

A relationship should not be implemented using a nested table if:

• It is often necessary to create or delete a lot of them (it takes too much time);
  
• There are few tables in general, or the database structure as a whole is simple (the relationship will take too much time to write the procedures for adding and deleting them);
  
• The logic of the data implies the equivalence of the connected objects (it is impossible to decide in which table to create a column of relationships).
  

It makes sense to think about implementing the relationship in an alternative way, if

• One table is connected on a many-to-many basis with several others (simplifies understanding of the structure and logic of data);
  
• Relationships are created and deleted quite rarely, and records in one and the linked tables are deleted and added not too often (there is no loss in speed);
  
• To work with the database, only procedures, representations and, possibly, sampling are used;
  
• There is a need to implement a foreign key on several tables, and not on one (a more elegant solution, unlike creating multiple columns, especially since the function of checking the correspondence of the field identifier is needed in any case).