Background:
I am building a web application that allows Teachers to Quiz their students with many analytical features to help gauge individual/class performance on test/quizzes. Currently, I am in the middle of redesigning the MySQL DB Schema and you can see a rudimentary EEF diagram of what I've built thus far included:
I am actually using Hibernate to create the connection from my backend to my DB but this EEF Diagram does a good job of showing the relationships between the tables.
The Problem I currently have separate tables for Admins, Students, and Teachers because all 3 will have unique fields even though, currently, they have pretty much similar fields. The issue with having 3 different tables, is that I want Teachers and Students to share some functionality. For instance, a Teacher should be able to take a quiz just like a student(for testing purposes). My first instinct is to make a User class that both Student and Teacher extend:
public class User{
...
}
@Entity
@Table(name="student")
public class Student extends User{
...
}
@Entity
@Table(name="teacher")
public class Teacher extends User{
...
}
This approach accomplishes what I want to do with my quiz taking service:
public QuizInstance takeQuiz(User u, QuizStructure struct){
...
}
but I am unsure how I create the mapping relationships when there is polymorphism involved. A good example of this issue is with the registerUser service:
public void registerUser(User u, String password) {
if(u instanceof Teacher) {
//register User into Teacher Table..
}else if(u instanceof Student) {
//register User into Student Table...
}
new LoginService().createLogin(u, password);
}
public class LoginService{
...
public void createLogin(User u, String password){
Login l = new Login();
l.setEncrPass(encrypt(password));
l.setEmail(u.getEmail());
if(u instanceof Teacher)
l.setRole("teacher");
else
l.setRole("student");
l.setUser(u);
//Save Login into Login Table in DB
}
}
@Entity
@Table(name="login")
public class Login {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="user_id", unique=true, nullable=false)
private int id;
@Column(name="email", nullable=false)
private String email;
@Column(name="encrypted_pass", nullable=false)
private String encryptedPass;
@Column(name="role", nullable=false)
private String role;
//How do I map User if it can link to either the Teacher table or the Student Table???
private User user;
}
I question if this is the best approach to solving the issue. I can't help but shake that the design of my DB Schema is an issue, security and structure-wise. Can someone tell me if the DB Architecture I've constructed is poor craftmanship and if so share some examples/resources that better help illustrate more secure Architectures for DBs.