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 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:
enter image description here
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 ;
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:
enter image description here
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 ;
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:
enter image description here
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 ;
- 145.6k
- 22
- 190
- 479
Referential MySql Design Database of survey information
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:
enter image description here
MySQL Code: 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 ;
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?
Referential MySql Design
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:
enter image description here
MySQL Code:
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 ;
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?
Database of survey information
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:
enter image description here
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 ;
- 13.8k
- 12
- 57
- 127