Switch to: V12V11V10V9V8V7V6V5

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.


Final State

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


Transition Steps

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

Create MM Table

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.

API Method(s)

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

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

API Example

' 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 )

SQL Command(s)

COPY LINKS FROM blink_name TO link_to_left_name AND link_to_right_name

SQL Example

-- 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;