Database design

Database design is one of the most important skill for a software engineer. You will be working with data in-and-out and is important to understand how you can effectively design a database. In this article, I am going to focus on relation database design (MySQL). In the next few articles I will cover how the same design can be converted to NoSQL databases.

For this article we will focus our discussion on building a Course management system for a university. First thing to do is note down all the requirements something like:

• A departments can offer multiple courses.
• A course can have multiple classes.
• Students can enroll into multiple classes.

From the requirements, let’s create an ER diagram. ER diagrams helps capture the entities, attributes and their relationship with each other. Please refer to ER diagram wiki for more information.

Now, relations between entities can be of:

• One-To-One
• One-To-Many or Many-To-One
• Many-To-Many (this one is bit tricky to model sometimes)

One-To-Many/ Many-To-One relationship

Let’s first look at the second case — “A course can have multiple classes”. It means that there is one to many relationship between course and classes. For instance, “Database Systems Course” could have multiple classes in Fall 2022, or Spring Spring 2022 by different faculty. Let’s look at how the code would look like in Hibernate.

Let’s define our Course entity :

@Entity
public class Course {
    @Id
    @GeneratedValue
    private Long courseId;
 
    @NaturalId
    private String courseName;
 
    @OneToMany(
        mappedBy = "course",
        cascade = CascadeType.ALL
    )
    
    private List<Class> classes;

    @ManyToOne
    private Department department;
}

Now, we can define Class entity:

@Entity
public class Class {
    @Id
    @GeneratedValue
    private Long classId;
 
    @NaturalId
    private String className;
 
    @ManyToOne
    private Course course;

    private String year;

    private String semseter;

    private String startDate;
    
    private String endDate;

    private String teacher;
}

Department class should look something like this:

@Entity
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class Department {
    @Id
    @GeneratedValue
    private Long departmentId;
 
    @NaturalId
    private String departmentName;
 
    @OneToMany
    private List<Course> courses;
}

Many To Many relationships

Now, we will look into the harder part of database designs — “Students can enroll into multiple classes.” To work with Many To Many relationship we would need to define an intermediary table that maps between the two tables. So, let’s say we have two entities students and class, and we want to define many to many relationship then we can define Enrollment as another entity that maps students & class.

@Entity
public class Student {
    @Id
    @GeneratedValue
    private Long studentId;
 
    private String studentName;
 
    private String firstName;
    
    private String lastName;
    
    @OneToMany(mappedBy="student")
    private Student student;
}

@Embeddable
@EqualsAndHashcode
@ToString
public class EnrollmentId implements Serializable {
 
    @Column(name = "student_id")
    private Long studentId;
 
    @Column(name = "course_id")
    private Long classId;
 
    private EnrollmentId() {}
 
    public EnrollmentId(
        Long studentId,
        Long courseId) {
        this.studentId = studentId;
        this.courseId = courseId;
    }
}

@Entity
@Embeddable
public class Enrollment {
    @Id
    private EnrollmentId enrollmentId;
    
    @ManyToOne
    private Student student;

    @ManyToOne
    private Class class;

    private Timestamp dateOfEnrollment;
    
    private Grade grade;
}

You can find the full code here —

https://github.com/sagar-arora/course-system