Following a meeting with JW, made a range of changes to the db:

• Added a new select field and associated table for "status".
• Added a new select field and associated table for "grouping".
• Replaced the "approaches" tables with "themes" tables (same data, but new table and field names).
• Renamed the "notes" column to "synopsis".
• Added a new one-to-many field and two associated tables for "language".
• Added four new comments fields, one each for these one-to-many fields: document type, language, theme, and authorship.
• Updated the local_classes.php and the copy_live_to_dev_db.sql files to support all the changes.

This is the SQL:

# Create the status table

CREATE TABLE IF NOT EXISTS `status` (
  `st_id` int(11) NOT NULL auto_increment,
  `st_name` varchar(128) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`st_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

# Add a record to it
INSERT INTO `status` (st_name) VALUES ("active");

# Add the field that links to it

ALTER TABLE `revolt` ADD COLUMN `rv_status` INT(11) AFTER `rv_id`;

# Add the constraint.

ALTER TABLE `revolt` 
ADD CONSTRAINT `revolt_ibfk_4` FOREIGN KEY (`rv_status`) REFERENCES `status` (`st_id`) ON DELETE SET NULL ON UPDATE CASCADE;


# Create a new themes table based on the existing approaches table

CREATE TABLE IF NOT EXISTS `theme` (
  `th_id` int(11) NOT NULL auto_increment,
  `th_name` varchar(128) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`th_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=9 ;

# Copy the data into it.

INSERT INTO `theme` (`th_id`, `th_name`) SELECT `ap_id`, `ap_name` FROM `approach`;



# Create a new theme_to_revolt table based on the existing approach_to_revolt table

CREATE TABLE IF NOT EXISTS `theme_to_revolt` (
  `thr_thr_id` int(11) NOT NULL auto_increment,
  `thr_th_id_fk` int(11) NOT NULL,
  `thr_rv_id_fk` int(11) NOT NULL,
  PRIMARY KEY  (`thr_thr_id`),
  KEY `thr_th_id_fk` (`thr_th_id_fk`),
  KEY `thr_rv_id_fk` (`thr_rv_id_fk`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=132 ;

# Copy the data over to the new table

INSERT INTO `theme_to_revolt` (`thr_thr_id`, `thr_th_id_fk`, `thr_rv_id_fk`) SELECT `apr_apr_id`, `apr_ap_id_fk`, `apr_rv_id_fk` FROM `approach_to_revolt`;


#Rename the notes field to synopsis

ALTER TABLE `revolt` CHANGE `rv_notes` `rv_synopsis` longtext collate utf8_unicode_ci;


#Add three new columns
ALTER TABLE `revolt` ADD `rv_themeComments` varchar(2048) collate utf8_unicode_ci default NULL;
ALTER TABLE `revolt` ADD `rv_docTypeComments` varchar(2048) collate utf8_unicode_ci default NULL;
ALTER TABLE `revolt` ADD `rv_authorshipComments` varchar(2048) collate utf8_unicode_ci default NULL;



# Create the language table

CREATE TABLE IF NOT EXISTS `lang` (
  `ln_id` int(11) NOT NULL auto_increment,
  `ln_name` varchar(128) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`ln_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

# Add a record to it
INSERT INTO `lang` (ln_name) VALUES ("English");

# Create the linking table.

# Create a new theme_to_revolt table based on the existing approach_to_revolt table

CREATE TABLE IF NOT EXISTS `lang_to_revolt` (
  `lnr_lnr_id` int(11) NOT NULL auto_increment,
  `lnr_ln_id_fk` int(11) NOT NULL,
  `lnr_rv_id_fk` int(11) NOT NULL,
  PRIMARY KEY  (`lnr_lnr_id`),
  KEY `lnr_ln_id_fk` (`lnr_ln_id_fk`),
  KEY `lnr_rv_id_fk` (`lnr_rv_id_fk`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

# Add the constraints

ALTER TABLE `lang_to_revolt`
  ADD CONSTRAINT `lang_to_revolt_ibfk_1` FOREIGN KEY (`lnr_rv_id_fk`) REFERENCES `revolt` (`rv_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `lang_to_revolt_ibfk_2` FOREIGN KEY (`lnr_ln_id_fk`) REFERENCES `lang` (`ln_id`) ON DELETE CASCADE ON UPDATE CASCADE;


#Add new language comment column
ALTER TABLE `revolt` ADD `rv_langComments` varchar(2048) collate utf8_unicode_ci default NULL;



# Create new grouping table
CREATE TABLE IF NOT EXISTS `grouping` (
  `gr_id` int(11) NOT NULL auto_increment,
  `gr_name` varchar(128) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`gr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

# Add a record to it
INSERT INTO `grouping` (gr_name) VALUES ("[none]");

# Add the field that links to it

ALTER TABLE `revolt` ADD COLUMN `rv_grouping` INT(11) AFTER `rv_region`;

# Add the constraint.

ALTER TABLE `revolt` 
ADD CONSTRAINT `revolt_ibfk_5` FOREIGN KEY (`rv_grouping`) REFERENCES `grouping` (`gr_id`) ON DELETE SET NULL ON UPDATE CASCADE;


#Drop the tables we no longer need.
DROP TABLE `approach_to_revolt`;
DROP TABLE `approach`;