Convert M:M Table to BinaryLink M:M

Let you have tables Person and Phone linked as M:M with the help of third table, i.e. the only way to provide M:M link of two tables.

• Both tables have ID fields that most probably are Primary Key also.
• Third table PersonPhone_MM has two fields that point two tables correspondingly. Most probably Foreign Keys are built over these fields.
• It is easy to see that exists two links between three tables on picture.

Or you can have very similar structure but MM table contains ObjectPtr fields.

You can see that structure is practically the same. Just absent ID fields (PKs), because RecID field is used instead (natural PK for any table). We still have two links for that three tables.

Let you want switch from MM table to Binary Link to get Binary Link advantages.

In this case you can drop:

• ID fields in tables (PKs)
• MM (“glue”) table.

But use a M::M BinaryLink between tables Person and Phone instead.

To switch from original state to target state do the following steps.

• Create Binary Link between tables Person and Phone as M:M. At this moment it is empty.

• Do loop by smaller table, e.g. Person as:
  • Go to Nth record of the Person table.
  • Find linked records in the table Phone using two jumps by links.
  • Having pairs {Person.RecID, Phone.RecID} add them into BinaryLink.

This step is natural although optional.

• Drop Table PersonPhoneMM.
• Optionally drop ID fields in tables Person and Phone.

Valentina offer API method which is able to execute above loop in the most efficient way.

VLink2.CopyLinksFrom( linkToLeftTable as VLink2, linkToRightTable as VLink2 )

COPY LINKS FROM link_to_left_name AND link_to_right_name TO blink_name

-- STEP 1: Create Binary Link
CREATE BINARY LINK linkPersonPhone_MM 
    ON TABLES(Person, Phone) AS MANY TO MANY;
 
-- STEP 2: Copy Links from MM Table into Binary Link
COPY LINKS FROM linkToPerson AND linkToPhone TO linkPersonPhone_MM;
 
-- STEP 3:
DROP TABLE PersonPhoneMM;
 
-- STEP 4: optional, if ID fields present.
ALTER TABLE tblPerson DROP COLUMN ID;
ALTER TABLE tblPhone  DROP COLUMN ID;