Database of survey information

Question

How does this look? It has been over a year since my database concepts class and I have never worked with MySQL. I have chosen InnoDB as my engine because of foreign key support using UTF-8 based on advice from an earlier review. Everything works as it should when deleting and updating, but I am worried I am doing something that may be considered bad practice.

The main table Tracker manages different survey name and descriptions. The table Card holds information on each person who has been surveyed. Survey holds the results of each Card response for the survey being tracked by Tracker and has a unique composite key.

If a cardID is deleted or updated the corresponding composite key of Survey will be deleted or updated. If a trackID is deleted or updated this will delete or update the corresponding cardID fields in Card.

ERD design image:

Is this the right way to apply a composite key in this way? Are there any changes that should be made? Are there any suggestions for changes?

CREATE TABLE `Tracker` (
 `trackID` int(11) NOT NULL AUTO_INCREMENT,
 `tName` varchar(40) DEFAULT NULL,
 `tDesc` varchar(200) DEFAULT NULL,
 PRIMARY KEY (`trackID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

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(50) NOT NULL,
 `isMember` int(1) NOT NULL,
 PRIMARY KEY (`cardID`),
 FOREIGN KEY (`trackID`) REFERENCES `Tracker`(`trackID`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE `Survey` (
 `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 (`cardID`, `trackID`),
 FOREIGN KEY (`trackID`) REFERENCES `Tracker`(`trackID`) ON UPDATE CASCADE,
 FOREIGN KEY (`cardID`) REFERENCES `Card`(`cardID`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

Answer 1

I agree with Corbin and Ignacio Vazquez-Abrams, and here are some other notes:

The main table Tracker manages different survey name and descriptions.

Then, I'd call it Survey with attributes surveyId, name and description.

The table Card holds information on each person who has been surveyed.

Then, I'd call it SurveyedPerson with attributes surveryPersonId etc.

Survey holds the results of each Card response for the survey being tracked by Tracker and has a unique composite key.

Then, SurveyResult (or Answer) holds the results of each SurveyPerson response for the Survey (being tracked by Survey.surveyId) and has a unique composite key.

In your first question I did not understand the purpose of these tables (see my comment on your first question). I don't know whether your original names are special terms in the field of the survey science or anything but the names above seem easier to understand, read and therefore maintain (for me, at least).

---

1. Are you sure that you need the Card.trackId attribute? It seems duplication of Survey.trackId (so it's redundant).

2. Are you sure that you don't need to store multiple Surveys (answers) for the same Card (person)? What happens when the same person answers to multiple surveys? Do you duplicate their name?

3. Attribute lengths in the Card table seems too short. You might have problems with the new name of this guy. AFAIK email addresses also could be 254 characters long.

Answer 2

Just a quick thing: int(1) probably does not mean what you think it means.

mysql> create table int_length (x int(1));
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO int_length VALUES (1234);
Query OK, 1 row affected (0.05 sec)

mysql> SHOW WARNINGS;
Empty set (0.00 sec)

mysql> SELECT x FROM int_length;
+------+
| x    |
+------+
| 1234 |
+------+
1 row in set (0.00 sec)

int(x) is basically used for zerofill and nothing else. If you want a smaller data type, you'll need to use a small data type (tinyint, etc). (Note that this doesn't apply to var types. varchar(5) is <= 5 characters, and varchar(10) is <= 10 characters.

Answer 3

You should consider normalizing Survey further, in that having many fields with only a differing numeric suffix indicates that it should be a single field with another key.

CREATE TABLE `Survey` (
 `cardID` int(11) NOT NULL,
 `trackID` int(11) NOT NULL,
 `questionID` int(11) NOT NULL,
 `response` int(1) NOT NULL,
 PRIMARY KEY (`cardID`, `trackID`, `questionID`),
 FOREIGN KEY (`trackID`) REFERENCES `Tracker`(`trackID`) ON UPDATE CASCADE,
 FOREIGN KEY (`cardID`) REFERENCES `Card`(`cardID`) ON UPDATE CASCADE ON DELETE CASCADE,
 FOREIGN KEY (`questionID`) REFERENCES `Question`(`questionID`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;