1
\$\begingroup\$

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 ;
\$\endgroup\$
  • \$\begingroup\$ Could you write something about the specification? Some sample data also could help. \$\endgroup\$ – palacsint Aug 13 '12 at 19:15
  • \$\begingroup\$ Should you not be using InnoDB if you want Foreign Key restraints? \$\endgroup\$ – David Barker Aug 13 '12 at 23:11
  • \$\begingroup\$ @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. \$\endgroup\$ – Brandon Clark Aug 14 '12 at 0:31
2
\$\begingroup\$
  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.

\$\endgroup\$
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Brandon Clark Aug 13 '12 at 20:57
  • \$\begingroup\$ @BrandonClark: Sorry, I don't know MySQL so deep. It may be worth a question on dba.stackexchange.com \$\endgroup\$ – palacsint Aug 15 '12 at 6:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.