Skip to main content
Code Review

Return to Question

replaced http://codereview.stackexchange.com/ with https://codereview.stackexchange.com/
Source Link

A modified review request has been made here. 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 ;

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 ;

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 ;
deleted 226 characters in body; edited title
Source Link

MySQL database design reviewwith foreign key support

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? It has been awhile since I wrote a database and I have a feeling something I am doing is not right. I have included my ERD though a link since I am a new user and code for the MySQL database (it works). Any suggestions?

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 ;

MySQL database design review

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? It has been awhile since I wrote a database and I have a feeling something I am doing is not right. I have included my ERD though a link since I am a new user and code for the MySQL database (it works). Any suggestions?

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 ;

MySQL database with foreign key support

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 ;
added 141 characters in body
Source Link

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? It has been awhile since I wrote a database and I have a feeling something I am doing is not right. I have included my ERD though a link since I am a new user and code for the MySQL database (it works). Any suggestions?

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 ;

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? It has been awhile since I wrote a database and I have a feeling something I am doing is not right. I have included my ERD though a link since I am a new user and code for the MySQL database (it works). Any suggestions?

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 ;

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? It has been awhile since I wrote a database and I have a feeling something I am doing is not right. I have included my ERD though a link since I am a new user and code for the MySQL database (it works). Any suggestions?

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 ;
Tweeted twitter.com/#!/StackCodeReview/status/235297995174862848
improves formatting
Source Link
palacsint
  • 30.4k
  • 9
  • 82
  • 157
Loading
edited body
Source Link
Loading
deleted 179 characters in body
Source Link
Loading
added 10 characters in body
Source Link
Loading
deleted 9 characters in body
Source Link
Loading
Source Link
Loading
lang-sql

AltStyle によって変換されたページ (->オリジナル) /