Re: Chamging a MySQL schema
I got these from the mysql client:
| person | CREATE TABLE `person` (
`Person_ID` int(11) NOT NULL AUTO_INCREMENT,
`Artist_ID` int(11) NOT NULL DEFAULT '0',
`Last_Name` varchar(45) DEFAULT NULL,
`First_Name` varchar(45) DEFAULT NULL,
`Middle_Name` varchar(45) DEFAULT NULL,
`Suffix` varchar(5) DEFAULT NULL,
`Comment` varchar(255) DEFAULT NULL,
`Group_ID` int(11) DEFAULT NULL,
`Voice_ID` int(11) DEFAULT NULL,
`Birth_Date` date DEFAULT NULL,
`Death_Date` date DEFAULT NULL,
PRIMARY KEY (`Person_ID`),
KEY `FK_person_1` (`Artist_ID`),
KEY `FK_person_2_idx` (`Group_ID`),
KEY `FK_person_3_idx` (`Voice_ID`),
CONSTRAINT `FK_person_1` FOREIGN KEY (`Artist_ID`) REFERENCES `artist` (`Artist_ID`),
CONSTRAINT `FK_person_2` FOREIGN KEY (`Group_ID`) REFERENCES `group` (`Group_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_person_3` FOREIGN KEY (`Voice_ID`) REFERENCES `voice` (`Voice_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3885 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
| artist | CREATE TABLE `artist` (
`Artist_ID` int(11) NOT NULL AUTO_INCREMENT,
`Type` enum('G','P') NOT NULL DEFAULT 'P' COMMENT 'G=Group, P=Person',
`Name` varchar(255) NOT NULL DEFAULT ' ',
`Comment` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Artist_ID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4046 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
| artist_role | CREATE TABLE `artist_role` (
`Artist_Role_ID` int(11) NOT NULL AUTO_INCREMENT,
`Artist_ID` int(11) NOT NULL DEFAULT '0',
`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',
PRIMARY KEY (`Artist_Role_ID`) USING BTREE,
KEY `FK_artist_role_1` (`Artist_ID`),
KEY `FK_artist_role_2` (`Role_Code`),
CONSTRAINT `FK_artist_role_1` FOREIGN KEY (`Artist_ID`) REFERENCES `artist` (`Artist_ID`),
CONSTRAINT `FK_artist_role_2` FOREIGN KEY (`Role_Code`) REFERENCES `role` (`Role_Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4477 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
| group | CREATE TABLE `group` (
`Group_ID` int(11) NOT NULL AUTO_INCREMENT,
`Artist_ID` int(11) NOT NULL DEFAULT '0',
`Name` varchar(60) NOT NULL DEFAULT ' ',
`Qualifier` varchar(25) DEFAULT ' ',
`Comment` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Group_ID`),
KEY `FK_group_1` (`Artist_ID`),
CONSTRAINT `FK_group_1` FOREIGN KEY (`Artist_ID`) REFERENCES `artist` (`Artist_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=262 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
| musical_work | CREATE TABLE `musical_work` (
`Musical_Work_ID` int(11) NOT NULL AUTO_INCREMENT,
`Title` varchar(255) NOT NULL DEFAULT ' ',
`Comment` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Musical_Work_ID`) USING BTREE,
KEY `Title_Indx` (`Title`)
) ENGINE=InnoDB AUTO_INCREMENT=6825 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
| musical_work_composer | CREATE TABLE `musical_work_composer` (
`Musical_Work_ID` int(11) NOT NULL DEFAULT '0',
`Artist_Role_ID` int(11) NOT NULL DEFAULT '0',
`Comment` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Musical_Work_ID`,`Artist_Role_ID`) USING BTREE,
KEY `FK_musical_work_composer_2` (`Artist_Role_ID`) USING BTREE,
CONSTRAINT `FK_musical_work_composer_1` FOREIGN KEY (`Musical_Work_ID`) REFERENCES `musical_work` (`Musical_Work_ID`),
CONSTRAINT `FK_musical_work_composer_2` FOREIGN KEY (`Artist_Role_ID`) REFERENCES `artist_role` (`Artist_Role_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Writers or composers of the musical work' |
| performance | CREATE TABLE `performance` (
`Performance_ID` int(11) NOT NULL AUTO_INCREMENT,
`Musical_Work_ID` int(11) NOT NULL DEFAULT '0',
`Comment` varchar(255) DEFAULT NULL,
`Volume_ID` int(11) DEFAULT NULL,
`Disc_No` int(11) DEFAULT NULL,
`Side` int(11) DEFAULT NULL,
`Track_No` int(11) DEFAULT NULL,
`Date` date DEFAULT NULL,
`Location` varchar(255) DEFAULT NULL,
`Master` enum('Y','N','U') NOT NULL DEFAULT 'U',
`Matrix` varchar(255) DEFAULT NULL,
`Take` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Performance_ID`),
KEY `FK_performance_1` (`Musical_Work_ID`),
CONSTRAINT `FK_performance_1` FOREIGN KEY (`Musical_Work_ID`) REFERENCES `musical_work` (`Musical_Work_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=8975 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Musical works performances' |
| performance_artist | CREATE TABLE `performance_artist` (
`Performance_ID` int(11) NOT NULL DEFAULT '0',
`Artist_Role_ID` int(11) NOT NULL DEFAULT '0',
`Comment` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Performance_ID`,`Artist_Role_ID`) USING BTREE,
KEY `FK_performance_artist_2` (`Artist_Role_ID`),
CONSTRAINT `FK_performance_artist_1` FOREIGN KEY (`Performance_ID`) REFERENCES `performance` (`Performance_ID`),
CONSTRAINT `FK_performance_artist_2` FOREIGN KEY (`Artist_Role_ID`) REFERENCES `artist_role` (`Artist_Role_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Artists by performances' |
| volume | CREATE TABLE `volume` (
`Volume_ID` int(11) NOT NULL AUTO_INCREMENT,
`Title` varchar(255) NOT NULL DEFAULT ' ',
`Artist_ID` int(11) NOT NULL DEFAULT '0',
`Group_ID` int(11) NOT NULL DEFAULT '0',
`Label_ID` int(11) NOT NULL DEFAULT '0',
`Catalog_Number` varchar(45) DEFAULT NULL,
`Year_Published` char(4) DEFAULT NULL,
`Month_Published` enum('01','02','03','04','05','06','07','08','09','10','11','12') DEFAULT NULL,
`Media_Type` enum('CD','LP','EP','45') NOT NULL DEFAULT 'CD',
`Number_Discs` int(10) unsigned NOT NULL,
`Classification_ID` int(11) NOT NULL DEFAULT '3',
`Comment` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Volume_ID`),
KEY `Label_Indx` (`Label_ID`),
KEY `FK_volume_1` (`Artist_ID`),
KEY `FK_volume_3` (`Classification_ID`),
CONSTRAINT `FK_volume_1` FOREIGN KEY (`Artist_ID`) REFERENCES `artist` (`Artist_ID`),
CONSTRAINT `FK_volume_2` FOREIGN KEY (`Label_ID`) REFERENCES `label` (`Label_ID`),
CONSTRAINT `FK_volume_3` FOREIGN KEY (`Classification_ID`) REFERENCES `classification` (`Classification_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=645 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='Volume Information' |
| volume_artist | CREATE TABLE `volume_artist` (
`Volume_ID` int(11) NOT NULL,
`Artist_ID` int(11) NOT NULL,
`Comment` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Volume_ID`,`Artist_ID`),
KEY `FK_volume_artist_1_idx` (`Artist_ID`),
CONSTRAINT `FK_volume_artist_1` FOREIGN KEY (`Artist_ID`) REFERENCES `artist` (`Artist_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_volume_artist_2` FOREIGN KEY (`Volume_ID`) REFERENCES `volume` (`Volume_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| track | CREATE TABLE `track` (
`Volume_ID` int(11) NOT NULL DEFAULT '0',
`Disc_No` int(11) NOT NULL DEFAULT '0',
`Side` int(11) NOT NULL DEFAULT '0',
`Track_No` int(11) NOT NULL DEFAULT '0',
`Performance_ID` int(11) NOT NULL DEFAULT '0',
`Track_Start_Time` time DEFAULT NULL,
`Track_Length` time DEFAULT NULL,
`Track_Format` enum('M','S','E') NOT NULL DEFAULT 'S',
`Comment` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Volume_ID`,`Disc_No`,`Side`,`Track_No`),
KEY `FK_track_2` (`Performance_ID`),
CONSTRAINT `FK_track_1` FOREIGN KEY (`Volume_ID`) REFERENCES `volume` (`Volume_ID`),
CONSTRAINT `FK_track_2` FOREIGN KEY (`Performance_ID`) REFERENCES `performance` (`Performance_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
I believe this is what you need.