MySQL database with foreign key support

Question

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:

1. When a cardID is deleted this cascades and deletes the surveyID associated row.
2. When a trackID is deleted this cascades and deletes the cardID associated row and its associated surveyID row.

ENTITY RELATIONSHIP DIAGRAM

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 ;

• palacsint: Could you write something about the specification? Some sample data also could help.
• David Barker: Should you not be using InnoDB if you want Foreign Key restraints?
• Brandon Clark: @DavidBarker I am pretty sure you are correct. I tried to switch the engine type but I need to figure out what I need to do different when creating the tables because switching the engine type doesn't work. Find it kind of funny that MyISAM accepts it but converts the foreign keys to keys.

Answer 1

1. I'd use longer attribute names (firstName instead of fName, middleName, lastName). It's easier to read and maintain.

2. Are you sure that latin1 is enough for every data? Consider using UTF-8.

• Brandon Clark: I have made the change to utf8. That was a very good suggestion. For now I will leave the names as is because this is my prototype or proof of concept for a application that is establishing a remote connection to the database. I will definitely take you advice when I get going on the working beta. What about the Engine? I know InnoDB has foreign key support But I am having an issue with my MySql if I try and switch engines, not sure why since key support is on.
• palacsint: @BrandonClark: Sorry, I don't know MySQL so deep. It may be worth a question on dba.stackexchange.com