Convert BinaryLink M:M to M:M Table with ObjectPtr

Let you have tables Person and Phone linked as M:M with the help of BinaryLink.

Assume, you want to switch from Binary Link to MM third table (“glue” table ) design, using two ObjectPtr fields in this table.

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

Create MM table with two ObjectPtr fields, the first points the Person table, the second points the Phone table.

Optionally you can create other columns in this table, but it does not matter for the current task.

• Loop by BinaryLink to get pairs {RecID1, RecID2} of linked records of tables Person and Phone. For each such pair:
  • Add a new record into PersonePhoneMM table.

You may drop Binary Link because now Person and Phone tables linked via third MM table PersonePhoneMM.

Valentina offers API method which is able to execute above loop in the most effective way.

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

' STEP 1: Create MM Table
tblPersonPhone = db.CreateTable( "tblPersonPhoneMM" )
tblPersonPhone.CreateObjectPtrField( "PersonPtr", tblPerson, kCascade, "linkToPerson" )
tblPersonPhone.CreateObjectPtrField( "PhonePtr",  tblPhone,  kCascade, "linkToPhone" )
 
' STEP 2: Copy Links
linkPersonPhone.CopyLinksTo( db.Link("linkToPerson"), db.Link("linkToPhone") )
 
' STEP 3: Drop Binary Link
db.DropLink( linkPersonPhone )

COPY LINKS FROM blink_name TO link_to_left_name AND link_to_right_name

-- STEP 1: Create MM Table
CREATE TABLE tblPersonPhoneMM(
    PersonPtr ObjectPtr REFERENCES tblPerson ON DELETE CASCADE,
    PhonePtr ObjectPtr REFERENCES tblPhone ON DELETE CASCADE);
 
-- STEP 2: Copy Links
COPY LINKS FROM linkPersonPhone TO linkToPerson AND linkToPhone;
 
-- STEP 3: Drop Binary Link
DROP LINK linkPersonPhone;