Attendance tracker design MySQL [closed]

Question

The task is to create a website which allows the admin to create classes, students and teachers. He/She should be able to assign students to classes and one teacher. The admin should also be able to register students attendance using a register. Classes should have specified dates as to when they are running.

The teachers that have been provided a login should be able to login, see the classes assigned to them and take the register. Any feedback would be appreciated!

CREATE TABLE IF NOT EXISTS admin_login (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(20) NOT NULL,
    password VARCHAR(40) NOT NULL
);

INSERT INTO admin_login (user_name, password) VALUES
('admin', SHA1('password')) ;

CREATE TABLE IF NOT EXISTS classes (
    class_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    class_name VARCHAR(30) NOT NULL,
    subject_name VARCHAR(30) NOT NULL,
    meeting_dates VARCHAR(900) NOT NULL,
    student_ids VARCHAR(900) NOT NULL,
    teacher_id VARCHAR(50) NOT NULL,
    completed BOOL,
    FOREIGN KEY (teacher_id)
        REFERENCES teachers (teacher_id)
); 

CREATE TABLE IF NOT EXISTS students (
    student_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name CHAR(30) NOT NULL,
    last_name CHAR(30) NOT NULL,
    profile_picture LONGBLOB NOT NULL
);

CREATE TABLE IF NOT EXISTS teachers (
    teacher_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name CHAR(30) NOT NULL,
    last_name CHAR(30) NOT NULL,
    profile_picture LONGBLOB NOT NULL,
    username VARCHAR(20) NOT NULL,
    password VARCHAR(40) NOT NULL
);

CREATE TABLE IF NOT EXISTS attendance (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT REFERENCES students (student_id),
    class_id INT,
    attendence_outcome CHAR,
    date CHAR
); 

Answer 1

A few thoughts:

1. You shouldn't have the student ID's as a varchar(900) in the classes table. This is a classic many to many relationship. You should create a bridge entity that will have a record for each class-student relationship. This will allow you to get all the student's in a class without needing to parse a varchar.

2. Instead of a varchar with all of the meeting date, you would do something similar to what I mentioned in regards to the students. In a separate table create a separate record for each meeting date.

3. What I mentioned in 1 and 2 will now help make the attendance table a bit more compact. The date and class_id can be removed and replaced using the unique id from the bridge entity that has all the classes meeting date.

4. In the classes table, the BOOL completed can be a calculated field by checking if the the last meeting date has already passed or not (ie. the current date > last meeting date).