Re: Chamging a MySQL schema
'person', 'CREATE TABLE `person` (\n `Person_ID` int(11) NOT NULL AUTO_INCREMENT,\n `Artist_ID` int(11) NOT NULL DEFAULT \'0\',\n `Last_Name` varchar(45) DEFAULT NULL,\n `First_Name` varchar(45) DEFAULT NULL,\n `Middle_Name` varchar(45) DEFAULT NULL,\n `Suffix` varchar(5) DEFAULT NULL,\n `Comment` varchar(255) DEFAULT NULL,\n `Group_ID` int(11) DEFAULT NULL,\n `Voice_ID` int(11) DEFAULT NULL,\n `Birth_Date` date DEFAULT NULL,\n `Death_Date` date DEFAULT NULL,\n PRIMARY KEY (`Person_ID`),\n KEY `FK_person_1` (`Artist_ID`),\n KEY `FK_person_2_idx` (`Group_ID`),\n KEY `FK_person_3_idx` (`Voice_ID`),\n CONSTRAINT `FK_person_1` FOREIGN KEY (`Artist_ID`) REFERENCES `artist` (`Artist_ID`),\n CONSTRAINT `FK_person_2` FOREIGN KEY (`Group_ID`) REFERENCES `group` (`Group_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,\n CONSTRAINT `FK_person_3` FOREIGN KEY (`Voice_ID`) REFERENCES `voice` (`Voice_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION\n) ENGINE=InnoDB AUTO_INCREMENT=3885 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC'
'artist', 'CREATE TABLE `artist` (\n `Artist_ID` int(11) NOT NULL AUTO_INCREMENT,\n `Type` enum(\'G\',\'P\') NOT NULL DEFAULT \'P\' COMMENT \'G=Group, P=Person\',\n `Name` varchar(255) NOT NULL DEFAULT \' \',\n `Comment` varchar(255) DEFAULT NULL,\n PRIMARY KEY (`Artist_ID`) USING BTREE\n) ENGINE=InnoDB AUTO_INCREMENT=4046 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC'
'artist_role', 'CREATE TABLE `artist_role` (\n `Artist_Role_ID` int(11) NOT NULL AUTO_INCREMENT,\n `Artist_ID` int(11) NOT NULL DEFAULT \'0\',\n `Role_Code` enum(\'A\',\'C\',\'D\',\'I\',\'P\',\'S\',\'V\',\'W\') NOT NULL DEFAULT \'V\' COMMENT \'A=Arranger, C=Conductor, D=Director, I=Instrumentalist, P=Producer, S=Speaker, V=Vocalist, W=Writer\',\n PRIMARY KEY (`Artist_Role_ID`) USING BTREE,\n KEY `FK_artist_role_1` (`Artist_ID`),\n KEY `FK_artist_role_2` (`Role_Code`),\n CONSTRAINT `FK_artist_role_1` FOREIGN KEY (`Artist_ID`) REFERENCES `artist` (`Artist_ID`),\n CONSTRAINT `FK_artist_role_2` FOREIGN KEY (`Role_Code`) REFERENCES `role` (`Role_Code`)\n) ENGINE=InnoDB AUTO_INCREMENT=4477 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC'
'musical_work', 'CREATE TABLE `musical_work` (\n `Musical_Work_ID` int(11) NOT NULL AUTO_INCREMENT,\n `Title` varchar(255) NOT NULL DEFAULT \' \',\n `Comment` varchar(255) DEFAULT NULL,\n PRIMARY KEY (`Musical_Work_ID`) USING BTREE,\n KEY `Title_Indx` (`Title`)\n) ENGINE=InnoDB AUTO_INCREMENT=6825 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC'
'group', 'CREATE TABLE `group` (\n `Group_ID` int(11) NOT NULL AUTO_INCREMENT,\n `Artist_ID` int(11) NOT NULL DEFAULT \'0\',\n `Name` varchar(60) NOT NULL DEFAULT \' \',\n `Qualifier` varchar(25) DEFAULT \' \',\n `Comment` varchar(255) DEFAULT NULL,\n PRIMARY KEY (`Group_ID`),\n KEY `FK_group_1` (`Artist_ID`),\n CONSTRAINT `FK_group_1` FOREIGN KEY (`Artist_ID`) REFERENCES `artist` (`Artist_ID`)\n) ENGINE=InnoDB AUTO_INCREMENT=262 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC'
'musical_work_composer', 'CREATE TABLE `musical_work_composer` (\n `Musical_Work_ID` int(11) NOT NULL DEFAULT \'0\',\n `Artist_Role_ID` int(11) NOT NULL DEFAULT \'0\',\n `Comment` varchar(255) DEFAULT NULL,\n PRIMARY KEY (`Musical_Work_ID`,`Artist_Role_ID`) USING BTREE,\n KEY `FK_musical_work_composer_2` (`Artist_Role_ID`) USING BTREE,\n CONSTRAINT `FK_musical_work_composer_1` FOREIGN KEY (`Musical_Work_ID`) REFERENCES `musical_work` (`Musical_Work_ID`),\n CONSTRAINT `FK_musical_work_composer_2` FOREIGN KEY (`Artist_Role_ID`) REFERENCES `artist_role` (`Artist_Role_ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT=\'Writers or composers of the musical work\''
'performance', 'CREATE TABLE `performance` (\n `Performance_ID` int(11) NOT NULL AUTO_INCREMENT,\n `Musical_Work_ID` int(11) NOT NULL DEFAULT \'0\',\n `Comment` varchar(255) DEFAULT NULL,\n `Volume_ID` int(11) DEFAULT NULL,\n `Disc_No` int(11) DEFAULT NULL,\n `Side` int(11) DEFAULT NULL,\n `Track_No` int(11) DEFAULT NULL,\n `Date` date DEFAULT NULL,\n `Location` varchar(255) DEFAULT NULL,\n `Master` enum(\'Y\',\'N\',\'U\') NOT NULL DEFAULT \'U\',\n `Matrix` varchar(255) DEFAULT NULL,\n `Take` varchar(255) DEFAULT NULL,\n PRIMARY KEY (`Performance_ID`),\n KEY `FK_performance_1` (`Musical_Work_ID`),\n CONSTRAINT `FK_performance_1` FOREIGN KEY (`Musical_Work_ID`) REFERENCES `musical_work` (`Musical_Work_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION\n) ENGINE=InnoDB AUTO_INCREMENT=8975 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT=\'Musical works performances\''
'performance_artist', 'CREATE TABLE `performance_artist` (\n `Performance_ID` int(11) NOT NULL DEFAULT \'0\',\n `Artist_Role_ID` int(11) NOT NULL DEFAULT \'0\',\n `Comment` varchar(255) DEFAULT NULL,\n PRIMARY KEY (`Performance_ID`,`Artist_Role_ID`) USING BTREE,\n KEY `FK_performance_artist_2` (`Artist_Role_ID`),\n CONSTRAINT `FK_performance_artist_1` FOREIGN KEY (`Performance_ID`) REFERENCES `performance` (`Performance_ID`),\n CONSTRAINT `FK_performance_artist_2` FOREIGN KEY (`Artist_Role_ID`) REFERENCES `artist_role` (`Artist_Role_ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT=\'Artists by performances\''
'volume', 'CREATE TABLE `volume` (\n `Volume_ID` int(11) NOT NULL AUTO_INCREMENT,\n `Title` varchar(255) NOT NULL DEFAULT \' \',\n `Artist_ID` int(11) NOT NULL DEFAULT \'0\',\n `Group_ID` int(11) NOT NULL DEFAULT \'0\',\n `Label_ID` int(11) NOT NULL DEFAULT \'0\',\n `Catalog_Number` varchar(45) DEFAULT NULL,\n `Year_Published` char(4) DEFAULT NULL,\n `Month_Published` enum(\'01\',\'02\',\'03\',\'04\',\'05\',\'06\',\'07\',\'08\',\'09\',\'10\',\'11\',\'12\') DEFAULT NULL,\n `Media_Type` enum(\'CD\',\'LP\',\'EP\',\'45\') NOT NULL DEFAULT \'CD\',\n `Number_Discs` int(10) unsigned NOT NULL,\n `Classification_ID` int(11) NOT NULL DEFAULT \'3\',\n `Comment` varchar(255) DEFAULT NULL,\n PRIMARY KEY (`Volume_ID`),\n KEY `Label_Indx` (`Label_ID`),\n KEY `FK_volume_1` (`Artist_ID`),\n KEY `FK_volume_3` (`Classification_ID`),\n CONSTRAINT `FK_volume_1` FOREIGN KEY (`Artist_ID`) REFERENCES `artist` (`Artist_ID`),\n CONSTRAINT `FK_volume_2` FOREIGN KEY (`Label_ID`) REFERENCES `label` (`Label_ID`),\n CONSTRAINT `FK_volume_3` FOREIGN KEY (`Classification_ID`) REFERENCES `classification` (`Classification_ID`)\n) ENGINE=InnoDB AUTO_INCREMENT=645 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT=\'Volume Information\''
'volume_artist', 'CREATE TABLE `volume_artist` (\n `Volume_ID` int(11) NOT NULL,\n `Artist_ID` int(11) NOT NULL,\n `Comment` varchar(255) DEFAULT NULL,\n PRIMARY KEY (`Volume_ID`,`Artist_ID`),\n KEY `FK_volume_artist_1_idx` (`Artist_ID`),\n CONSTRAINT `FK_volume_artist_1` FOREIGN KEY (`Artist_ID`) REFERENCES `artist` (`Artist_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,\n CONSTRAINT `FK_volume_artist_2` FOREIGN KEY (`Volume_ID`) REFERENCES `volume` (`Volume_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION\n) ENGINE=InnoDB DEFAULT CHARSET=utf8'
'track', 'CREATE TABLE `track` (\n `Volume_ID` int(11) NOT NULL DEFAULT \'0\',\n `Disc_No` int(11) NOT NULL DEFAULT \'0\',\n `Side` int(11) NOT NULL DEFAULT \'0\',\n `Track_No` int(11) NOT NULL DEFAULT \'0\',\n `Performance_ID` int(11) NOT NULL DEFAULT \'0\',\n `Track_Start_Time` time DEFAULT NULL,\n `Track_Length` time DEFAULT NULL,\n `Track_Format` enum(\'M\',\'S\',\'E\') NOT NULL DEFAULT \'S\',\n `Comment` varchar(255) DEFAULT NULL,\n PRIMARY KEY (`Volume_ID`,`Disc_No`,`Side`,`Track_No`),\n KEY `FK_track_2` (`Performance_ID`),\n CONSTRAINT `FK_track_1` FOREIGN KEY (`Volume_ID`) REFERENCES `volume` (`Volume_ID`),\n CONSTRAINT `FK_track_2` FOREIGN KEY (`Performance_ID`) REFERENCES `performance` (`Performance_ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC'
As you can see, there are some tables that are associated with artist but not person. I think my biggest area of concern is the relationship between person and artist.
I was wondering about the following approach:
1. Add person_id to the artist table;
2. Set person.person_id as a foreign key in the artist table;
3. Backup the schema;
4. Populate the artist table with the appropriate person_id (not sure how or where to do this as yet);
5. Alter the DDL as needed;
6. Reload the database.
Obviously, I would need to preserve the original schema before doing the steps above. Then the schema after the above steps would be the test/production (should everything work as required by use cases).