Propagate new set pairs to all children pairs in a

Question

I have a table which is similar to the following:

serial, integer1, integer2

There is constraint that integer1 != integer2, and the sets (1, 2) and (2, 1) cannot coexist (because they are the same set, in a different order).

Is there an easy way so that I can do the following:

When ever I insert an identifier pair, it will propagate the relationship down to all of each identifier's other pairs. For instance:

Start with:

(1, 2)
(3, 4)

If I insert (1, 3); automatically make (1, 4) (2, 4) and (2, 3). Would be nice if there is some select trickery that can be performed, but I think it will probably need to be a trigger to actually physically create those rows. Any suggestions for the best way to do this?

I want this to be ultra recursive.

Answer 1

Personally, I'd say the best option is to create a table group, and a table serial. The group table would be where you say integer 1 and 2 are part of the group A and integer 2, 3, and 4 are part of the group B. The serial table would be a list of all "coordinates" within a group and the associated serial.

From there, you can make a trigger on your group table when inserting, deleting and updating, where you propagate the changes to your serial table (either disable the now extinct coordinates or delete them, and then create new entries with a null or default serial, could even be generated through a stored procedure or a trigger on the serial table).

The reason that I would work with groups is because it allows you to manage deletion more easily. If you don't split the table, it's gonna be rough to remove 1 from the group A.

So basically :

• Table integer (if appropriate) : Integer + other valuable data
• Table group (a pseudo-junction table between integer and integer) : Integer, Group
• Table Serial (the actual junction table between integer and integer) : Integer, Integer, Serial + other valuable data. All inserts and removes should be done automatically by triggers on the table group.