MySQL Forums
Forum List  »  Newbie

Re: Chamging a MySQL schema
Posted by: Cecil Carpenter
Date: December 18, 2019 12:59PM

'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).

Options: ReplyQuote


Subject
Written By
Posted
December 17, 2019 11:12PM
December 18, 2019 12:00PM
December 18, 2019 02:03PM
December 18, 2019 02:20PM
December 18, 2019 03:01PM
December 18, 2019 09:25PM
December 18, 2019 10:11PM
December 19, 2019 10:06AM
December 18, 2019 04:05PM
Re: Chamging a MySQL schema
December 18, 2019 12:59PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.