Structural alteration to Trials db
Posted by mholmes on 27 Aug 2012 in Activity log
At JW's request, converted the Court field in the main Trial table from a single item to a one-to-many. Here's the SQL:
/*Create the new linking table.*/ CREATE TABLE IF NOT EXISTS `courts_to_trials` ( `ctt_ctt_id` int(11) NOT NULL auto_increment, `ctt_tr_id_fk` int(11) default NULL, `ctt_ct_id_fk` int(11) default NULL, PRIMARY KEY (`ctt_ctt_id`), KEY `ctt_ibfk_1` (`ctt_tr_id_fk`), KEY `ctt_ibfk_2` (`ctt_ct_id_fk`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ; /*Add constraints to the new linking table.*/ ALTER TABLE `courts_to_trials` ADD CONSTRAINT `ctt_ibfk_1` FOREIGN KEY (`ctt_tr_id_fk`) REFERENCES `trial` (`tr_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `ctt_ibfk_2` FOREIGN KEY (`ctt_ct_id_fk`) REFERENCES `court` (`ct_id`) ON DELETE CASCADE ON UPDATE CASCADE; /*Copy existing data.*/ INSERT INTO `courts_to_trials` (`ctt_tr_id_fk`, `ctt_ct_id_fk`) (SELECT `tr_id`, `tr_court` FROM `trial`); /*Delete original field.*/ ALTER TABLE `trial` DROP FOREIGN KEY `tr_ibfk_1`; ALTER TABLE `trial` DROP COLUMN `tr_court`;
Updated the local_classes.php file appropriately. Tested on dev, then ran on live. In the process of testing, found a little bug: when deleting a record, the table that comes back still has all the old column-header filter fields in it. I'll fix that now.