Hi Mark,
On Fri, Mar 23, 2012 at 7:29 PM, Mark Phillips
<mark@phillipsmarketing.biz> wrote:
I have a servlet/jsp/MySQL web site in production, and
there are about 2,000 records in the flights table. One of the foreign
keys is teacher_id. Up to this point, there is a one to many
relationship between teacher_id and the data in the flights table. I
need to change the data model to allow for a many to many relationship
between teacher_id and the data in the flight table. What is the best
way to do this?
Thanks,
Mark
The conversion procedure itself relatively simple:
1) Backup everything in case something goes wrong.
Code:
mkdir ~/tmp
cd ~/tmp
mysqldump -u root -p database > database.bck.sql;
(cd /public/www/whatever/..; tar pczv database) > database.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_something
accounts_foo_bar
4) Run "desc accounts_foo_bar" to make sure that it's the join table used
for the relationship.
Join tables look like this.
Code:
mysql> desc accounts_foo_bar
+---------------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+-------------+------+-----+---------+-------+
| id | varchar(36) | NO | PRI | NULL | |
| date_modified | datetime | YES | | NULL | |
| deleted | tinyint(1) | YES | | 0 | |
| accounts_bccounts_ida | varchar(36) | YES | MUL | NULL | |
| accounts_ccounts_idb | varchar(36) | YES | | NULL | |
+---------------------------+-------------+------+-----+---------+-------+
5) Save the data from the table using sqldump;
Code:
mysqldump -u root -p --database database --table accounts_foo_bar > ~/tmp/accounts_foo_bar.sql
Look at the ~/tmp/accounts_foo_bar.sql file and verify that it has a insert statement to restore your data.
6) Go into navicat or MySQL Studio, select the Accounts module, select the Relationships tab and delete the one-to-many
relationship between Accounts and Satellite Foo. 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 Navicat or Studio
do all the heavy lifting for us.
This can take a while, be sure to let it finish.
7) White still in the MySQL Studio, on the Accounts module Relationships tab, add a new Many-to-Many relationship
between Accounts and Satellite Foo.
8) View the main Accounts tab, and verify that there is a "Satellite Foo" subpanel. View the Satelite Foo tab and verify that there is an Accounts subpanel.
9) Edit the ~/tmp/accounts_foo_bar.sql file, deleting everything except the "insert" statement.
10) In mysql, source the ~/tmp/accounts_foo_bar.sql file to restore your relationship data.
11)Now go back to the Accounts model, you will notice that the Satellite Foo subpanel now displays all the related Satellite Foo.
You might have to add some grants or open some ports to allow 3306 access for Navicat (which runs in wine great) or MySQL Studio. Email me off list for specific questions.
--
(503) 754-4452 Android
(623) 239-3392 Skype
(623) 688-3392 Google Voice
**
it-clowns.com