A modified review request has been made here.
Can I please have someone review this simple database design below and tell me if this is the correct way to achieve what I want to do?
What I am trying to do:
- When a
cardID
is deleted this cascades and deletes thesurveyID
associated row. - When a
trackID
is deleted this cascades and deletes thecardID
associated row and its associatedsurveyID
row.
MYSQL CODE
CREATE TABLE `Card` (
`cardID` int(11) NOT NULL AUTO_INCREMENT,
`trackID` int(11) NOT NULL,
`fName` varchar(21) NOT NULL,
`mName` varchar(1) DEFAULT NULL,
`lName` varchar(21) DEFAULT NULL,
`email` varchar(40) NOT NULL,
`isMember` int(1) NOT NULL,
PRIMARY KEY (`cardID`),
FOREIGN KEY (`trackID`) REFERENCES `Tracker`(`trackID`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
CREATE TABLE `Survey` (
`surveyID` int(11) NOT NULL AUTO_INCREMENT,
`cardID` int(11) NOT NULL,
`trackID` int(11) NOT NULL,
`q0` int(1) NOT NULL,
`q1` int(1) DEFAULT NULL,
`q2` int(1) DEFAULT NULL,
`q3` int(1) DEFAULT NULL,
`q4` int(1) DEFAULT NULL,
`q5` int(1) DEFAULT NULL,
PRIMARY KEY (`surveyID`),
FOREIGN KEY (`trackID`) REFERENCES `Tracker`(`trackID`) ON UPDATE CASCADE,
FOREIGN KEY (`cardID`) REFERENCES `Card`(`cardID`)ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
CREATE TABLE `Tracker` (
`trackID` int(11) NOT NULL AUTO_INCREMENT,
`tName` varchar(21) DEFAULT NULL,
`tDesc` varchar(50) DEFAULT NULL,
PRIMARY KEY (`trackID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;