Convert 1:M Link to M:M Table

Let you have two tables Person and Phone linked as 1:M with help of

• A.1 RDB link or

• A.2 ObjectPtr link.

Let you have found that tables Person and Phone should be linked as M:M. You have 3 choices now:

• B.1 Implement M:M with the help of third table based on RDB links.

• B.2 Implement M:M with the help of third table based on ObjectPtr link.

i.e. you need to provide changes in the database structure to be able store future M:M links, and convert existed 1:M links into this new structure. Note, that since you extend power of link from 1:M to M:M you cannot catch any conflicts here.

It is easy to see that we have 4 paths of transformation between initial and final states:

• A.1 ⇒ B.1
• A.1 ⇒ B.2
• A.2 ⇒ B.1
• A.2 ⇒ B.2

For case B.1

Create MM table with two fields that has types corresponded to ID fields of tables Person and Phone. Specify two Foreign Key links based on these fields to point Person and Phone tables.

For case B.2

Create MM table 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 this not matter for the current task.

• Loop by Person (ONE) Table.
  • For each Person record
    • Get its RecID of ID1 value
    • Find set of linked childs in Phone table
    • Obtains RecID or ID values for that Phone records
    • Each pair {ID1, ID2} or {RecID1, RecID2} insert into PersonPhoneMM table.

Drop original RDB or ObjectPtr Link in the Phone table.

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

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

COPY LINKS FROM link_name TO link_to_left_name AND link_to_right_name

For Case A.1 ⇒ B.1

-- STEP 1: Create MM Table with 2 Foreign Keys
CREATE TABLE tblPersonPhoneMM(
    PersonPtr ULONG 
        FOREIGN KEY linkToPerson 
        REFERENCES tblPerson ON DELETE CASCADE ON UPDATE CASADE,
    PhonePtr ULONG 
        FOREIGN KEY linkToPhone 
        REFERENCES tblPhone ON DELETE CASCADE ON UPDATE CASADE )
 
-- STEP 2: Copy Links from 1:M ObjectPtr into two Foreign Key links 
COPY LINKS FROM linkToPerson1M TO linkToPerson AND linkToPhone
 
-- STEP 3: Drop 1:M ObjectPtr in the Phone table
ALTER TABLE Phone DROP COLUMN fldPersonPtr

For Case A.2 ⇒ B.2

-- 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 from Original 1:M ObjectPtr into two new ObjectPtrs of MM Table 
COPY LINKS FROM linkToPerson1M TO linkToPerson AND linkToPhone;
 
-- STEP 3: Drop 1:M ObjectPtr in the Phone table
ALTER TABLE Phone DROP COLUMN fldPersonPtr