Changing from One-to-Many to Many-to-Many using Studio and SQL

**markriggins** · 2012-02-21, 06:15 PM

Have you ever made a mistake in your data model, and found out too late, after you already have a good bit of data stored in the database that the relationship is wrong?

I ran into this, and needed to change a relationship from "One-to-Many" to "Many-to-Many". I've seen some other posts that talk about editing metadata files and such, but that sounded a bit complicated to me, so I tried something much easier and it worked. This may really help those of you who know a little bit of SQL, but are not able to handle PHP programming.

For my example, lets assume I added a "Satellite Office" Module to Sugar, using the Studio tools, and created a One-to-Many relationship from Accounts, so each Account can have more than one "Satellite Office". Later, I discovered that these Satellite Offices are actually shared by facilities that may be used by more than one Account.

STEPS TO CONVERT FROM One-to-Many to Many-to-Many

Please note: Since your module is not named "Satellite Offices" you will have to substitute the actual name of your module in the instructions below. If your database isn't named 'sugarcrm' you'll have to substitute the actual name of your database. And if your sugarcrm website lives someplace other than /public/www/sugarcm, you have to substitute the actual name and location of your sugarcrm website instance. Finally, if you're using some other database than MySQL you will have to consult the documentation for that database to find out how to save and restore the contents of a table.

1) Backup everything in case something goes wrong.

Code:


mkdir ~/tmp
cd ~/tmp
mysqldump -u root -p sugarcrm > sugarcrm.bck.sql;
(cd /public/www/sugarcrm/..; tar pczv sugarcrm)  >  sugarcrm.tar.gz

2) Use a SQL tool, such as mysql to access the database directly.

3) Run the "show tables" command to find out the name of your join table.

Code:

mysql> show tables;
accounts
accounts_cstm
accounts_satellite_office

4) Run "desc accounts_satellite_office" to make sure that it's the join table used
for the relationship.

Join tables look like this.

Code:

mysql> desc accounts_satellite_offices
+---------------------------+-------------+------+-----+---------+-------+
| Field                     | Type        | Null | Key | Default | Extra |
+---------------------------+-------------+------+-----+---------+-------+
| id                        | varchar(36) | NO   | PRI | NULL    |       |
| date_modified             | datetime    | YES  |     | NULL    |       |
| deleted                   | tinyint(1)  | YES  |     | 0       |       |
| accounts_x85beccounts_ida | varchar(36) | YES  | MUL | NULL    |       |
| accounts_x2903ccounts_idb | varchar(36) | YES  |     | NULL    |       |
+---------------------------+-------------+------+-----+---------+-------+

5) Save the data from the table using sqldump;

Code:

mysqldump -u root -p --database sugarcrm --table accounts_satellite_offices > ~/tmp/accounts_satellite_offices.sql

Look at the ~/tmp/accounts_satellite_offices.sql file and verify that it has a insert statement to restore your data.

6) Go into Studio, select the Accounts module, select the Relationships tab and delete the one-to-many
relationship between Accounts and Satellite Offices. Click the little checkbox that says "delete the table" -- don't worry, you have all the data backed up. The table name and schema will be recreated in the next step (7), but the new table will have different indexes -- we're going to let the Studio do all the heavy lifting for us.

This can take a while, be sure to let it finish.

7) White still in the Studio, on the Accounts module Relationships tab, add a new Many-to-Many relationship
between Accounts and Satellite Offices.

8) View the main Accounts tab, and verify that there is a "Satellite Offices" subpanel. View the Satelite Offices tab and verify that there is an Accounts subpanel.

9) Edit the ~/tmp/accounts_satellite_offices.sql file, deleting everything except the "insert" statement.

10) In mysql, source the ~/tmp/accounts_satellite_offices.sql file to restore your relationship data.

11)Now go back to the Accounts model, you will notice that the Satellite Offices subpanel now displays all the related Satellite Offices.

That's it. No coding necessary. Just a little SQL.