Database design for school attendance and scheduling

I work at a small English school in Japan and I'm trying to create a database to handle student attendance and class scheduling. I'm using PostgreSQL for the database and I'll use PHP for interacting with it. I've looked at some examples of similar designs here and elsewhere for guidance, but since I'm still an amateur, I'd like to see if I've made any major mistakes before I take this into production.

First, a diagram (full resolution):

Next, the SQL code (generated by GenMyModel.com):

```
-- Create schemas
CREATE SCHEMA IF NOT EXISTS System;

CREATE SCHEMA IF NOT EXISTS Attendance;

CREATE SCHEMA IF NOT EXISTS People;

CREATE SCHEMA IF NOT EXISTS Classes;

-- Create tables
-- Users of the database, aka staff members who are allowed to enter attendance data
CREATE TABLE IF NOT EXISTS System.users
(
user_id SERIAL NOT NULL,
person_id SERIAL NOT NULL, -- Links a user to their staff information
user_type SERIAL NOT NULL,
username VARCHAR(100) NOT NULL,
password VARCHAR(500) NOT NULL,
salt VARCHAR(500) NOT NULL,
last_login TIMESTAMP,
created TIMESTAMP NOT NULL,
lastmod TIMESTAMP NOT NULL,
PRIMARY KEY(user_id)
);

CREATE TABLE IF NOT EXISTS System.user_types
(
utype_id SERIAL NOT NULL,
utype_name VARCHAR(100) NOT NULL, -- Admin vs. standard user
utype_desc VARCHAR(500),
created TIMESTAMP NOT NULL,
lastmod TIMESTAMP NOT NULL,
PRIMARY KEY(utype_id)
);

CREATE TABLE IF NOT EXISTS Attendance.makeup
(
makeup_id SERIAL NOT NULL,
student_id SERIAL NOT NULL,
original_cinstance_id SERIAL NOT NULL,
makeup_cinstance_id SERIAL NOT NULL,
notes VARCHAR(1000),
created TIMESTAMP NOT NULL,
lastmod TIMESTAMP NOT NULL,
PRIMARY KEY(makeup_id)
);

CREATE TABLE IF NOT EXISTS Attendance.attendance
(
attendance_id SERIAL NOT NULL,
cinstance_id SERIAL NOT NULL,
teacher_id SERIAL NOT NULL,
student_id SERIAL NOT NULL,
present BOOLEAN NOT NULL,
notes VARCHAR(1000),
create

You would be better served to create Stored Procedures to handle the insertion of data into the database. This would also give you the option of giving people a store proc so as to help them enter data but not give them actual access to the database.

You would then have the option to set certain permissions as the need may be. Maybe most teachers may want the right to enter data into the database but maybe only the Head Master and the system admin should have the authority to remove data.

Also, I would not do any of this without a clear security plan in place. In my country of origin, there is a Protection of User Information act that prohibits institutions of processing any information about a minor without the express permission of the child in questions guardian or parent.

There may also be certain legislation in place that may force you to have to partake in security audits. You are dealing with a database that has people's addresses on, that is sensitive enough information that if that leaks you and your institution may be liable, the fact that there are minors involved only makes this more of an iffy proposition.

My advice to you would be just to either employ someone who is qualified to do this or just to simply walk away.

StackExchange Code Review Q#157947, answer score: 2