Database design for school attendance and scheduling

Question

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),
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(attendance_id)
);

CREATE TABLE IF NOT EXISTS Attendance.grade_instances
(
    ginstance_id SERIAL NOT NULL,
    gtype_id SERIAL NOT NULL,
    attendance_id SERIAL NOT NULL,
    grade CHARACTER(1) NOT NULL,
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(ginstance_id)
);

CREATE TABLE IF NOT EXISTS Attendance.grade_types
(
    gtype_id SERIAL NOT NULL,
    gtype_name VARCHAR(100) NOT NULL,
    gtype_desc VARCHAR(1000),
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(gtype_id)
);

-- List all the students expected to be in each class
CREATE TABLE IF NOT EXISTS Attendance.roster
(
    person_id SERIAL NOT NULL,
    class_id SERIAL NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE DEFAULT infinity NOT NULL,
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(person_id, class_id)
);

CREATE TABLE IF NOT EXISTS People.address_types
(
    atype_id SERIAL NOT NULL,
    atype_name VARCHAR(500) NOT NULL, -- Home vs. work, etc.
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(atype_id)
);

CREATE TABLE IF NOT EXISTS People.addresses
(
    address_id SERIAL NOT NULL,
    address VARCHAR(500) NOT NULL,
    postal_code VARCHAR(10) NOT NULL,
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(address_id)
);

CREATE TABLE IF NOT EXISTS People.person_types
(
    ptype_id SERIAL NOT NULL,
    ptype_name VARCHAR(100) NOT NULL, -- Students vs. staff
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(ptype_id)
);

CREATE TABLE IF NOT EXISTS People.people
(
    person_id SERIAL NOT NULL,
    family_nameK VARCHAR(100) NOT NULL, -- Kanji/katakana
    given_nameK VARCHAR(100) NOT NULL, -- Kanji/katakana
    famliy_nameR VARCHAR(100) NOT NULL, -- Romaji
    given_nameR VARCHAR(100) NOT NULL, -- Romaji
    dob DATE NOT NULL,
    gender_id SERIAL NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE DEFAULT infinity NOT NULL, -- Default of infinity until they quit
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(person_id)
);

CREATE TABLE IF NOT EXISTS People.genders
(
    gender_id SERIAL NOT NULL,
    gender_name VARCHAR(100) NOT NULL,
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(gender_id)
);

CREATE TABLE IF NOT EXISTS People.people2addresses
(
    person_id SERIAL NOT NULL,
    address_id SERIAL NOT NULL,
    atype_id SERIAL NOT NULL,
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(person_id, address_id)
);

CREATE TABLE IF NOT EXISTS People.discounts
(
    discount_id SERIAL NOT NULL,
    discount_name VARCHAR(100) NOT NULL, -- Family discount, corp. discount, etc.
    discount_amt INTEGER NOT NULL, -- Percentage off the price
    discount_notes VARCHAR(500),
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(discount_id)
);

CREATE TABLE IF NOT EXISTS People.people2discounts
(
    person_id SERIAL NOT NULL,
    discount_id SERIAL NOT NULL,
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(person_id, discount_id)
);

CREATE TABLE IF NOT EXISTS People.people2phone_numbers
(
    person_id SERIAL NOT NULL,
    phone_id SERIAL NOT NULL,
    ptype_id SERIAL NOT NULL,
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(person_id, phone_id)
);

CREATE TABLE IF NOT EXISTS People.people2banks
(
    person_id SERIAL NOT NULL,
    bank_id SERIAL NOT NULL,
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(person_id, bank_id)
);

CREATE TABLE IF NOT EXISTS People.banks
(
    bank_id SERIAL NOT NULL,
    bank_nameK VARCHAR(100) NOT NULL,
    bank_nameR VARCHAR(100),
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(bank_id)
);

CREATE TABLE IF NOT EXISTS People.phone_types
(
    ptype_id SERIAL NOT NULL,
    ptype_name VARCHAR(100) NOT NULL, -- Home, mobile, etc.
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(ptype_id)
);

CREATE TABLE IF NOT EXISTS People.phone_numbers
(
    phone_id SERIAL NOT NULL,
    phone_number VARCHAR(100) NOT NULL,
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(phone_id)
);

CREATE TABLE IF NOT EXISTS People.relationships
(
    person_id1 SERIAL NOT NULL,
    person_id2 SERIAL NOT NULL,
    rtype_id SERIAL NOT NULL,
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(person_id1, person_id2)
);

CREATE TABLE IF NOT EXISTS People.relationship_types
(
    rtype_id SERIAL NOT NULL,
    rtype_name VARCHAR(100) NOT NULL, -- Mother, father, sibling, etc.
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(rtype_id)
);

CREATE TABLE IF NOT EXISTS People.people2person_types
(
    person_id SERIAL NOT NULL,
    ptype_id SERIAL NOT NULL,
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(person_id, ptype_id)
);

CREATE TABLE IF NOT EXISTS People.email_addresses
(
    email_address_id SERIAL NOT NULL,
    email_address VARCHAR(500),
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(email_address_id)
);

CREATE TABLE IF NOT EXISTS People.people2email_addresses
(
    person_id SERIAL NOT NULL,
    email_address_id SERIAL NOT NULL,
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(person_id, email_address_id)
);

CREATE TABLE IF NOT EXISTS Classes.levels
(
    level_id SERIAL NOT NULL,
    level_name VARCHAR(100) NOT NULL,
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(level_id)
);

CREATE TABLE IF NOT EXISTS Classes.class_instances
(
    cinstance_id SERIAL NOT NULL,
    class_id SERIAL NOT NULL,
    cinstance_date DATE NOT NULL,
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(cinstance_id)
);

CREATE TABLE IF NOT EXISTS Classes.class_types
(
    ctype_id SERIAL NOT NULL,
    ctype_name VARCHAR(100) NOT NULL,
    class_length INTERVAL NOT NULL,
    private BOOLEAN NOT NULL,
    price MONEY NOT NULL,
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(ctype_id)
);

CREATE TABLE IF NOT EXISTS Classes.classes
(
    class_id SERIAL NOT NULL,
    level_id SERIAL NOT NULL,
    location_id SERIAL NOT NULL,
    dow_id SERIAL NOT NULL,
    class_time TIME NOT NULL,
    ctype_id SERIAL NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(class_id)
);

CREATE TABLE IF NOT EXISTS Classes.days_of_week
(
    dow_id SERIAL NOT NULL,
    dow_name VARCHAR(100) NOT NULL,
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(dow_id)
);

CREATE TABLE IF NOT EXISTS Classes.locations
(
    location_id SERIAL NOT NULL,
    location_name VARCHAR(100) NOT NULL,
    location_address VARCHAR(500) NOT NULL,
    location_phone VARCHAR(100) NOT NULL,
    created TIMESTAMP NOT NULL,
    lastmod TIMESTAMP NOT NULL,
    PRIMARY KEY(location_id)
);


-- Create FKs
ALTER TABLE Attendance.attendance
    ADD    FOREIGN KEY (cinstance_id)
    REFERENCES Classes.class_instances(cinstance_id)
    MATCH SIMPLE
;

ALTER TABLE Attendance.attendance
    ADD    FOREIGN KEY (teacher_id)
    REFERENCES People.people(person_id)
    MATCH SIMPLE
;

ALTER TABLE Attendance.attendance
    ADD    FOREIGN KEY (student_id)
    REFERENCES People.people(person_id)
    MATCH SIMPLE
;

ALTER TABLE Attendance.makeup
    ADD    FOREIGN KEY (student_id)
    REFERENCES People.people(person_id)
    MATCH SIMPLE
;

ALTER TABLE Attendance.makeup
    ADD    FOREIGN KEY (original_cinstance_id)
    REFERENCES Classes.class_instances(cinstance_id)
    MATCH SIMPLE
;

ALTER TABLE Attendance.makeup
    ADD    FOREIGN KEY (makeup_cinstance_id)
    REFERENCES Classes.class_instances(cinstance_id)
    MATCH SIMPLE
;

ALTER TABLE System.users
    ADD    FOREIGN KEY (user_type)
    REFERENCES System.user_types(utype_id)
    MATCH SIMPLE
;

ALTER TABLE Attendance.grade_instances
    ADD    FOREIGN KEY (attendance_id)
    REFERENCES Attendance.attendance(attendance_id)
    MATCH SIMPLE
;

ALTER TABLE Attendance.grade_instances
    ADD    FOREIGN KEY (gtype_id)
    REFERENCES Attendance.grade_types(gtype_id)
    MATCH SIMPLE
;

ALTER TABLE People.people
    ADD    FOREIGN KEY (gender_id)
    REFERENCES People.genders(gender_id)
    MATCH SIMPLE
;

ALTER TABLE People.people2addresses
    ADD    FOREIGN KEY (person_id)
    REFERENCES People.people(person_id)
    MATCH SIMPLE
;

ALTER TABLE People.people2addresses
    ADD    FOREIGN KEY (address_id)
    REFERENCES People.addresses(address_id)
    MATCH SIMPLE
;

ALTER TABLE People.people2addresses
    ADD    FOREIGN KEY (atype_id)
    REFERENCES People.address_types(atype_id)
    MATCH SIMPLE
;

ALTER TABLE People.people2discounts
    ADD    FOREIGN KEY (person_id)
    REFERENCES People.people(person_id)
    MATCH SIMPLE
;

ALTER TABLE People.people2discounts
    ADD    FOREIGN KEY (discount_id)
    REFERENCES People.discounts(discount_id)
    MATCH SIMPLE
;

ALTER TABLE People.people2phone_numbers
    ADD    FOREIGN KEY (person_id)
    REFERENCES People.people(person_id)
    MATCH SIMPLE
;

ALTER TABLE People.people2banks
    ADD    FOREIGN KEY (person_id)
    REFERENCES People.people(person_id)
    MATCH SIMPLE
;

ALTER TABLE People.people2banks
    ADD    FOREIGN KEY (bank_id)
    REFERENCES People.banks(bank_id)
    MATCH SIMPLE
;

ALTER TABLE People.people2phone_numbers
    ADD    FOREIGN KEY (ptype_id)
    REFERENCES People.phone_types(ptype_id)
    MATCH SIMPLE
;

ALTER TABLE People.people2phone_numbers
    ADD    FOREIGN KEY (phone_id)
    REFERENCES People.phone_numbers(phone_id)
    MATCH SIMPLE
;

ALTER TABLE Classes.classes
    ADD    FOREIGN KEY (ctype_id)
    REFERENCES Classes.class_types(ctype_id)
    MATCH SIMPLE
;

ALTER TABLE Classes.classes
    ADD    FOREIGN KEY (level_id)
    REFERENCES Classes.levels(level_id)
    MATCH SIMPLE
;

ALTER TABLE Classes.class_instances
    ADD    FOREIGN KEY (class_id)
    REFERENCES Classes.classes(class_id)
    MATCH SIMPLE
;

ALTER TABLE Classes.classes
    ADD    FOREIGN KEY (dow_id)
    REFERENCES Classes.days_of_week(dow_id)
    MATCH SIMPLE
;

ALTER TABLE People.relationships
    ADD    FOREIGN KEY (person_id1)
    REFERENCES People.people(person_id)
    MATCH SIMPLE
;

ALTER TABLE People.relationships
    ADD    FOREIGN KEY (person_id2)
    REFERENCES People.people(person_id)
    MATCH SIMPLE
;

ALTER TABLE People.relationships
    ADD    FOREIGN KEY (rtype_id)
    REFERENCES People.relationship_types(rtype_id)
    MATCH SIMPLE
;

ALTER TABLE People.people2person_types
    ADD    FOREIGN KEY (person_id)
    REFERENCES People.people(person_id)
    MATCH SIMPLE
;

ALTER TABLE People.people2person_types
    ADD    FOREIGN KEY (ptype_id)
    REFERENCES People.person_types(ptype_id)
    MATCH SIMPLE
;

ALTER TABLE People.people2email_addresses
    ADD    FOREIGN KEY (person_id)
    REFERENCES People.people(person_id)
    MATCH SIMPLE
;

ALTER TABLE People.people2email_addresses
    ADD    FOREIGN KEY (email_address_id)
    REFERENCES People.email_addresses(email_address_id)
    MATCH SIMPLE
;

ALTER TABLE Attendance.roster
    ADD    FOREIGN KEY (person_id)
    REFERENCES People.people(person_id)
    MATCH SIMPLE
;

ALTER TABLE Attendance.roster
    ADD    FOREIGN KEY (class_id)
    REFERENCES Classes.classes(class_id)
    MATCH SIMPLE
;

ALTER TABLE Classes.classes
    ADD    FOREIGN KEY (location_id)
    REFERENCES Classes.locations(location_id)
    MATCH SIMPLE
;

ALTER TABLE System.users
    ADD    FOREIGN KEY (person_id)
    REFERENCES People.people(person_id)
    MATCH SIMPLE
;


-- Create Indexes

A few notes:

• Names are in the database twice since we're keeping track of student/staff names in both Japanese characters (kanji/katakana) and English alphabet (romaji).
• The "relationships" table is for family members, like siblings, parents, etc. Since family names aren't terribly unique, it's good to have a way to find everyone from the same family for the purpose of discounts and such.
• If you're wondering why there's both "class_types" and "levels": the class types will be things like "Adult Private", "Child Group", etc. Length of class and price are directly based off these types. The levels are more specific, like "Elementary School, Level 1".

I have one specific question: Should I create schemas in the database? They were generated in the SQL code because I used "groups" in my diagram. I did that mainly for organizational reasons, so I could better visualize things. But one reason to use schemas is for logical separation, so that seems like an okay idea. But it does create some extra code when doing selects and inserts and I've never used them before.

And if there's any other advice you have, I'd be happy to hear it. Thanks!

EDIT: Something that has come up is an edge case for "classes". Almost all the classes adhere to a fixed schedule with regard to location, day of the week and time of day. But there are a few students we call "flex" who have no fixed schedule. They call in and ask when there's an opening during the week. Now, since the rest of the schedule is fairly rigid and the openings are regular, these flex students usually come at the same time on the same day of the week. But it's definitely not always.

I'm wondering how to handle these cases.

Answer 1

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.