Creating a 2-way relationship in PostgreSQL table

Question

I have 3 tables representing UUIDs, Name, Location, and Info of a house, room and drawers (this is an example as my work is sensitive).

So, for example 1 house will have many rooms (one to many) and the many rooms will contain many drawers (many to many).

The idea is that an associations table will be created where each UUID of the rows in the table will be associated with the corresponding UUID of the other table. For example, if I query the house which is represent by ID1 it will return the following:

SELECT * FROM house where 'ID_1='1';

| ID_1|ID_2  |
| ----| -----|
| 1   | 201  |
| 1   | 254  |
| 1   | 268  |

So far, I have created a temporary version of the associations table of how I need it to be represented in the real table. However, now I need a function to automatically fill in the IDs properly for all rows from the temporary associations table to the real associations table. For example:

INSERT INTO associations (id_1, id_2) VALUES
('1','201'),
('201','1')

I need it to be directionless so that when I query id_1 I'm also getting it's linked id_2 in the result

Answer 1

Let's say your query to get a one-way relationship looks like this:

SELECT room_uuid AS left_uuid, house_the_room_is_in_uuid AS right_uuid
FROM rooms
WHERE house_the_room_is_in_uuid IS NOT NULL
AND is_active

All you need to get the reverse relationship is to put the list in the other order; the rest of the query doesn't need to change, however complex it is:

SELECT house_the_room_is_in_uuid AS left_uuid, room_uuid AS right_uuid
FROM rooms
WHERE house_the_room_is_in_uuid IS NOT NULL
AND is_active

Both of those will be valid as queries to insert into a table with two UUID columns:

CREATE TABLE my_lookup_table (left_uuid UUID, right_uuid UUID);

INSERT INTO my_lookup_table (left_uuid, right_uuid)
SELECT ... -- either of the above

To combine them, either insert each into the same table in turn, or use a UNION to create one result set with both sets of rows:

SELECT room_uuid AS left_uuid, house_the_room_is_in_uuid AS right_uuid
FROM rooms
WHERE is_in_house_uuid IS NOT NULL
AND is_active

UNION

SELECT house_the_room_is_in_uuid AS left_uuid, room_uuid AS right_uuid
FROM rooms
WHERE is_in_house_uuid IS NOT NULL
AND is_active

All that's required for a union is that the queries have the same number and type of columns. The names (if relevant at all) come from the first query, but I find it more readable if you include the aliases on both.

Since the result of that UNION is itself just a two-column result set, it can be used with the same INSERT statement as before. That would allow you to insert into the table even if it had a self-referencing foreign key constraint as discussed here:

ALTER TABLE my_lookup_table ADD CONSTRAINT 
    my_lookup_table_combinations_must_be_unique
    UNIQUE (left_uuid, right_uuid);

ALTER TABLE my_lookup_table ADD CONSTRAINT 
    my_lookup_table_must_have_rows_both_ways_around
    FOREIGN KEY (right_uuid, left_uuid)
    REFERENCES my_lookup_table (left_uuid, right_uuid);

If you tried to insert just one set of rows, this would fail, but with the UNION, by the end of the statement/transaction, each row is in the table both ways around, so the constraint is met.