When designing your database you first need to identify all the entities that will be needed to support your goals. In your case you need to keep track of students and how their results in various courses/subjects.
Based on this we can identify the following entities:
- Subjects such as math,chemistry etc.
- Students who take tests and exams for those subjects.
- Staff members who are responsible for teaching the subjects and preparing the exams/test.
- Different types of tests i.e. class test, exams and so forth.
- The results of students for a particular type of test in a particular subject.
So based on this (rather simplified_ scenario we only need 5 tables. The second thing is to identify the relationships between the entities. Lets review them.
- A subject can only be presented by one staff member but a staff member can present many subjects. For example math is only taught by Mr Jones but Mr Jones teaches math and science. This is what we call a one to many relationship.
- A student can have many results in many subject and a subject can have many students with many results. For example Student 1 took an exam and a test in math with two different results. The math subject might have 100's of student each with one or more result recorded. This is a many to many relationship.
- Each result of a student will be of some type either a exam or a test. Thus there is one to many relationship here. There is only one exam type but math,science,geography and history all use this exam type.
So based on all of this have created a small demo database. I will take you through it.
Here is a visual representation of the structure:
Here is the code for the database.
CREATE SCHEMA IF NOT EXISTS `coursedb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `coursedb` ;
-- -----------------------------------------------------
-- Table `coursedb`.`staff`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `coursedb`.`staff` (
`staff_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`staff_name` VARCHAR(64) NOT NULL,
`staff_surname` VARCHAR(64) NOT NULL,
`staff_title` VARCHAR(64) NOT NULL,
PRIMARY KEY (`staff_ID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `coursedb`.`subject`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `coursedb`.`subject` (
`subject_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`subject_name` VARCHAR(64) NULL,
`staff_ID` INT UNSIGNED NOT NULL,
PRIMARY KEY (`subject_ID`),
INDEX `fk_subject_staff_idx` (`staff_ID` ASC),
CONSTRAINT `fk_subject_staff`
FOREIGN KEY (`staff_ID`)
REFERENCES `coursedb`.`staff` (`staff_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `coursedb`.`student`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `coursedb`.`student` (
`student_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`student_name` VARCHAR(64) NOT NULL,
PRIMARY KEY (`student_ID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `coursedb`.`subject_result_type`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `coursedb`.`subject_result_type` (
`subject_result_type_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`subject_result_type_name` VARCHAR(64) NOT NULL,
PRIMARY KEY (`subject_result_type_ID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `coursedb`.`subject_student_result`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `coursedb`.`subject_student_result` (
`subject_ID` INT UNSIGNED NOT NULL,
`student_ID` INT UNSIGNED NOT NULL,
`subject_student_result_date` DATE NOT NULL,
`subject_student_result_score` INT NOT NULL,
`subject_result_type_ID` INT UNSIGNED NOT NULL,
PRIMARY KEY (`subject_ID`, `student_ID`, `subject_student_result_date`),
INDEX `fk_subject_student_result_subject1_idx` (`subject_ID` ASC),
INDEX `fk_subject_student_result_student1_idx` (`student_ID` ASC),
INDEX `fk_subject_student_result_subject_result_type1_idx` (`subject_result_type_ID` ASC),
CONSTRAINT `fk_subject_student_result_subject1`
FOREIGN KEY (`subject_ID`)
REFERENCES `coursedb`.`subject` (`subject_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_subject_student_result_student1`
FOREIGN KEY (`student_ID`)
REFERENCES `coursedb`.`student` (`student_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_subject_student_result_subject_result_type1`
FOREIGN KEY (`subject_result_type_ID`)
REFERENCES `coursedb`.`subject_result_type` (`subject_result_type_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `coursedb`.`subject` (
`subject_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`subject_name` VARCHAR(64) NULL,
`staff_ID` INT UNSIGNED NOT NULL,
PRIMARY KEY (`subject_ID`),
INDEX `fk_subject_staff_idx` (`staff_ID` ASC),
CONSTRAINT `fk_subject_staff`
FOREIGN KEY (`staff_ID`)
REFERENCES `coursedb`.`staff` (`staff_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
Lets start adding some data so you can see how this all fits together. First lets add two staff members Mr Jones and Miss Grumpy.
Execute the following code:
INSERT INTO `coursedb`.`staff`
(
`staff_name`,
`staff_surname`,
`staff_title`
)
SELECT 'James', 'Jones','Mr'
UNION
SELECT 'Pot','Grumpy','Miss';
This will insert the two staff members. If you look at the newly inserted data by executing a SELECT statement you will find the following data:
staff_ID staff_name staff_surname staff_title
'1', 'James', 'Jones', 'Mr'
'2', 'Pot', 'Grumpy', 'Miss'
Notice how the database has assigned a ID number to these staff members? This is the primary key for the staff member and we use this to identify the row in the staff tables. I am keeping this to auto numbers for the moment but there are several options here.
We now need to create the courses that these two staff members teach. Mr Jones teaches math and science and Miss Grump teaches Art. Lets create the subjects and link them to the staff members. This can be done by the following code:
INSERT INTO `coursedb`.`subject`
(
`subject_name`,
`staff_ID`
)
VALUES
(
'Math',
1
);
INSERT INTO `coursedb`.`subject`
(
`subject_name`,
`staff_ID`
)
VALUES
(
'Science',
1
);
INSERT INTO `coursedb`.`subject`
(
`subject_name`,
`staff_ID`
)
VALUES
(
'Art',
2
);
You can now see which subjects Mr Jones teaches by executing the following query:
SELECT *
FROM staff stf
INNER JOIN `subject` sub
ON stf.staff_ID = sub.staff_ID
WHERE stf.staff_ID =1;
So before we can track how a student is doing in their courses there is two pieces of information that is missing. The type of result i.e. exam or test and of course some students. So lets add them.
INSERT INTO `coursedb`.`student`
(
`student_name`
)
VALUES
(
'Student 1'
);
INSERT INTO `coursedb`.`subject_result_type`
(
`subject_result_type_name`
)
SELECT 'Test'
UNION
SELECT 'Exam'
With this out of the way lets record some results for Student 1. Student one wrote 2 exams one in science one in math and Student 1 also did a art test. The following SQL will insert this data:
INSERT INTO `coursedb`.`subject_student_result`
(`subject_ID`,
`student_ID`,
`subject_student_result_date`,
`subject_student_result_score`,
`subject_result_type_ID`
)
VALUES
(
1,
1,
CURDATE(),
80,
2
);
INSERT INTO `coursedb`.`subject_student_result`
( `subject_ID`,
`student_ID`,
`subject_student_result_date`,
`subject_student_result_score`,
`subject_result_type_ID`
)
VALUES
(
2,
1,
CURDATE(),
60,
2
);
INSERT INTO `coursedb`.`subject_student_result`
( `subject_ID`,
`student_ID`,
`subject_student_result_date`,
`subject_student_result_score`,
`subject_result_type_ID`
)
VALUES
(
3,
1,
CURDATE(),
80,
1
);
You can now draw a report on a student and their results by executing the following query:
SELECT *
FROM subject_student_result ssr
INNER JOIN student std
ON ssr.student_ID = std.student_ID
INNER JOIN `subject` sub
ON ssr.subject_ID = sub.subject_ID
INNER JOIN subject_result_type srt
ON ssr.subject_result_type_ID = srt.subject_result_type_ID
INNER JOIN staff stf
ON sub.staff_ID = stf.staff_ID
I suggest you work through this model and really understand what I am showing you. It will make your designs simpler and cleaner and much less maintenance.
