0

I'm building a web application in PHP to handle attendance tracking at my small English school. I've got the database up and running with PostgreSQL and I've been importing data and working out the user interface for adding data. I'm still a beginner with this kind of thing, but I think I've planned the database out well.

But one issue is making me wonder. The way I have the database constructed, each instance of a class (as in the record of a regularly-scheduled class on a particular day) is used to link to the attendance data (student presence along with feedback) to the classes themselves (i.e. the schedule). What I'm wondering is at what point I should create each of these class instance records. Part of me thinks I should have a script create them in advance for the whole month or so. But the other part of me thinks they should be created on demand whenever data is entered requiring the use of a class instance record (i.e. attendance data).

If I create the records in advance, it streamlines the process of linking "makeup" lessons from the missed class to the makeup class. But then I might still have to create some records on demand if that makeup class is further into the future than I've accounted for.

If I create them on demand, it feels like the most efficient for the database, but it does create more work for each data entry point.

Am I doing something dumb here? Am I overthinking it? If there's some other, better way of doing things, I'm open to suggestions.

For reference, you can check out the full database diagram (I can't post photos yet as I'm new to this section of StackExchange). As for the SQL itself, here's the code:

-- Create tables
CREATE TABLE IF NOT EXISTS phone_numbers
(
    phone_id SERIAL NOT NULL,
    phone_number VARCHAR(100) NOT NULL UNIQUE,
    created TIMESTAMP DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(phone_id)
);

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

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

CREATE TABLE IF NOT EXISTS relationship_types
(
    rtype_id SERIAL NOT NULL,
    rtype_name VARCHAR(100) NOT NULL UNIQUE,
    created TIMESTAMP DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(rtype_id)
);

CREATE TABLE IF NOT EXISTS people2banks
(
    person_id SERIAL NOT NULL,
    bank_id SERIAL NOT NULL,
    note VARCHAR(100),
    created TIMESTAMP DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(person_id, bank_id)
);

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

CREATE TABLE IF NOT EXISTS people2email_addresses
(
    person_id SERIAL NOT NULL,
    email_address_id SERIAL NOT NULL,
    note VARCHAR(100),
    created TIMESTAMP DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(person_id, email_address_id)
);

CREATE TABLE IF NOT EXISTS phone_types
(
    ptype_id SERIAL NOT NULL,
    ptype_name VARCHAR(100) NOT NULL UNIQUE,
    created TIMESTAMP DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(ptype_id)
);

CREATE TABLE IF NOT EXISTS people2phone_numbers
(
    person_id SERIAL NOT NULL,
    phone_id SERIAL NOT NULL,
    ptype_id SERIAL NOT NULL,
    note VARCHAR(100),
    created TIMESTAMP DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(person_id, phone_id)
);

CREATE TABLE IF NOT EXISTS people
(
    person_id SERIAL NOT NULL,
    family_nameK VARCHAR(100) NOT NULL,
    given_nameK VARCHAR(100) NOT NULL,
    famliy_nameR VARCHAR(100) NOT NULL,
    given_nameR VARCHAR(100) NOT NULL,
    dob DATE NOT NULL,
    gender_id SERIAL NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE DEFAULT 'infinity' NOT NULL,
    created TIMESTAMP DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(person_id)
);

CREATE TABLE IF NOT EXISTS discounts
(
    discount_id SERIAL NOT NULL,
    discount_name VARCHAR(100) NOT NULL UNIQUE,
    discount_amt INTEGER NOT NULL,
    discount_notes VARCHAR(500),
    created TIMESTAMP DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(discount_id)
);

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

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

CREATE TABLE IF NOT EXISTS people2addresses
(
    person_id SERIAL NOT NULL,
    address_id SERIAL NOT NULL,
    atype_id SERIAL NOT NULL,
    note VARCHAR(100),
    created TIMESTAMP DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(person_id, address_id)
);

CREATE TABLE IF NOT EXISTS address_types
(
    atype_id SERIAL NOT NULL,
    atype_name VARCHAR(500) NOT NULL UNIQUE,
    created TIMESTAMP DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(atype_id)
);

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

CREATE TABLE IF NOT EXISTS person_types
(
    ptype_id SERIAL NOT NULL,
    ptype_name VARCHAR(100) NOT NULL UNIQUE,
    created TIMESTAMP DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(ptype_id)
);

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

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

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

CREATE TABLE IF NOT EXISTS 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 DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(attendance_id)
);

CREATE TABLE IF NOT EXISTS 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 DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(makeup_id)
);

CREATE TABLE IF NOT EXISTS 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 DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(person_id, class_id)
);

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

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

CREATE TABLE IF NOT EXISTS 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 DEFAULT 'infinity' NOT NULL,
    created TIMESTAMP DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(class_id)
);

CREATE TABLE IF NOT EXISTS 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 DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(ctype_id)
);

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

CREATE TABLE IF NOT EXISTS levels
(
    level_id SERIAL NOT NULL,
    level_name VARCHAR(100) NOT NULL UNIQUE,
    level_short_code VARCHAR(5),
    created TIMESTAMP DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(level_id)
);

CREATE TABLE IF NOT EXISTS users
(
    user_id SERIAL NOT NULL,
    person_id SERIAL NOT NULL,
    user_type SERIAL NOT NULL,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(500) NOT NULL,
    salt VARCHAR(500) NOT NULL,
    last_login TIMESTAMP,
    created TIMESTAMP DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(user_id)
);

CREATE TABLE IF NOT EXISTS user_types
(
    utype_id SERIAL NOT NULL,
    utype_name VARCHAR(100) NOT NULL UNIQUE,
    utype_desc VARCHAR(500),
    created TIMESTAMP DEFAULT current_timestamp NOT NULL,
    lastmod TIMESTAMP DEFAULT current_timestamp NOT NULL,
    PRIMARY KEY(utype_id)
);


-- Create FKs
ALTER TABLE attendance
    ADD    FOREIGN KEY (cinstance_id)
    REFERENCES class_instances(cinstance_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE attendance
    ADD    FOREIGN KEY (teacher_id)
    REFERENCES people(person_id)
    MATCH SIMPLE
    ON UPDATE CASCADE
;

ALTER TABLE attendance
    ADD    FOREIGN KEY (student_id)
    REFERENCES people(person_id)
    MATCH SIMPLE
    ON UPDATE CASCADE
;

ALTER TABLE makeup
    ADD    FOREIGN KEY (student_id)
    REFERENCES people(person_id)
    MATCH SIMPLE
    ON DELETE CASCADE
    ON UPDATE CASCADE
;

ALTER TABLE makeup
    ADD    FOREIGN KEY (original_cinstance_id)
    REFERENCES class_instances(cinstance_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE makeup
    ADD    FOREIGN KEY (makeup_cinstance_id)
    REFERENCES class_instances(cinstance_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE relationships
    ADD    FOREIGN KEY (person_id1)
    REFERENCES people(person_id)
    MATCH SIMPLE
    ON DELETE CASCADE
    ON UPDATE CASCADE
;

ALTER TABLE relationships
    ADD    FOREIGN KEY (person_id2)
    REFERENCES people(person_id)
    MATCH SIMPLE
    ON DELETE CASCADE
    ON UPDATE CASCADE
;

ALTER TABLE relationships
    ADD    FOREIGN KEY (rtype_id)
    REFERENCES relationship_types(rtype_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE people2person_types
    ADD    FOREIGN KEY (person_id)
    REFERENCES people(person_id)
    MATCH SIMPLE
    ON DELETE CASCADE
    ON UPDATE CASCADE
;

ALTER TABLE people2person_types
    ADD    FOREIGN KEY (ptype_id)
    REFERENCES person_types(ptype_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE people2email_addresses
    ADD    FOREIGN KEY (person_id)
    REFERENCES people(person_id)
    MATCH SIMPLE
    ON DELETE CASCADE
    ON UPDATE CASCADE
;

ALTER TABLE people2email_addresses
    ADD    FOREIGN KEY (email_address_id)
    REFERENCES email_addresses(email_address_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE roster
    ADD    FOREIGN KEY (person_id)
    REFERENCES people(person_id)
    MATCH SIMPLE
    ON DELETE CASCADE
    ON UPDATE CASCADE
;

ALTER TABLE roster
    ADD    FOREIGN KEY (class_id)
    REFERENCES classes(class_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE classes
    ADD    FOREIGN KEY (location_id)
    REFERENCES locations(location_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE users
    ADD    FOREIGN KEY (person_id)
    REFERENCES people(person_id)
    MATCH SIMPLE
    ON UPDATE CASCADE
;

ALTER TABLE people2phone_numbers
    ADD    FOREIGN KEY (phone_id)
    REFERENCES phone_numbers(phone_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE people2banks
    ADD    FOREIGN KEY (bank_id)
    REFERENCES banks(bank_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE people2banks
    ADD    FOREIGN KEY (person_id)
    REFERENCES people(person_id)
    MATCH SIMPLE
    ON DELETE CASCADE
    ON UPDATE CASCADE
;

ALTER TABLE people2phone_numbers
    ADD    FOREIGN KEY (ptype_id)
    REFERENCES phone_types(ptype_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE people2phone_numbers
    ADD    FOREIGN KEY (person_id)
    REFERENCES people(person_id)
    MATCH SIMPLE
    ON DELETE CASCADE
    ON UPDATE CASCADE
;

ALTER TABLE people
    ADD    FOREIGN KEY (gender_id)
    REFERENCES genders(gender_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE people2discounts
    ADD    FOREIGN KEY (person_id)
    REFERENCES people(person_id)
    MATCH SIMPLE
    ON DELETE CASCADE
    ON UPDATE CASCADE
;

ALTER TABLE people2addresses
    ADD    FOREIGN KEY (person_id)
    REFERENCES people(person_id)
    MATCH SIMPLE
    ON DELETE CASCADE
    ON UPDATE CASCADE
;

ALTER TABLE people2discounts
    ADD    FOREIGN KEY (discount_id)
    REFERENCES discounts(discount_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE people2addresses
    ADD    FOREIGN KEY (address_id)
    REFERENCES addresses(address_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE people2addresses
    ADD    FOREIGN KEY (atype_id)
    REFERENCES address_types(atype_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE grade_instances
    ADD    FOREIGN KEY (gtype_id)
    REFERENCES grade_types(gtype_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE grade_instances
    ADD    FOREIGN KEY (attendance_id)
    REFERENCES attendance(attendance_id)
    MATCH SIMPLE
    ON DELETE CASCADE
    ON UPDATE CASCADE
;

ALTER TABLE classes
    ADD    FOREIGN KEY (dow_id)
    REFERENCES days_of_week(dow_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE classes
    ADD    FOREIGN KEY (ctype_id)
    REFERENCES class_types(ctype_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE class_instances
    ADD    FOREIGN KEY (class_id)
    REFERENCES classes(class_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE classes
    ADD    FOREIGN KEY (level_id)
    REFERENCES levels(level_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

ALTER TABLE users
    ADD    FOREIGN KEY (user_type)
    REFERENCES user_types(utype_id)
    MATCH SIMPLE
    ON DELETE RESTRICT
    ON UPDATE CASCADE
;

migrated from stackoverflow.com Apr 11 '17 at 19:23

This question came from our site for professional and enthusiast programmers.

1

In your database, you have

  • class with contains general information about a course (usual timing, for when to when it happens, etc...)
  • class instance which is the occurence of a course on a specific day

There is no single best solution of when to create the instance records in your database. The answer to your question depends also on the purpose of your instance.

Option 1: create the instance in advance

In this option, once you've created the class, you create all the occurrences.

This raises several issues:

  • when to create the instances: Either immediately as soon as you've created the class record, or overnight, or through a special transaction (e.g. when the school director has finished his planning).
  • how to keep the instances in sync with the class record when it is updated (e.g. if the end date is longer, generate additional occurrences)
  • how to deal with updates of the class record that is incompatible with already recorded presences (i.e. how to handle history: is a class update retroactive or not)

This synchronisation issue is in general more complex than expected. SO I'd advise to go for this option, if there are clear benefits, i.e. in the following cases:

  • the class record is just a simplification for data entry, because in reality the instances are the meaningful elements (i.e. instances in the holiday period are deleted, specific instance could be rescheduled if the trainer is absent, etc...)
  • the class (e.g. theoretical schedule of the course) is stable
  • the occurence is also used for managing resources in advance (e.g. allocate rooms, and teachers)
  • scheduling rules could be more complex (e.g. course happens every week except the last week in the month).

Option 2: generate just in time

In this option, you create instances only when you need them. Typically, you could:

  • generate the instances of the day,
  • generate the instances of the week,
  • generate an instance if it doesn't exist and a presence is recorded for it.

The 2 first are easily programmed in a periodic job, or in a special command triggered by the training administrator. The last one makes the SQL command for attendance registration a little more complex, but you can be sure that there is no problem with the management of the historical data (e.g. if s.o. updates the class, nothing has to happen: the change is only valid for future; instances for which attendance was registered are not expected to be updated anymore)

Conclusion

Looking at your database scheme, my first impression would suggest to create the attendance data just in time. Maybe the job for generating overnight the instances for the next day could be the easiest to implement.

  • Thank you for the detailed response! You raise an issue in Option 1 that I had thought about earlier but forgot to mention: unnecessary instances. If I create them in large batches (a month or more at a time), it's likely that I'll create instances which will later need to be removed due to a holiday. I suppose I could also create a database object to store regular holidays somehow, but that might be overthinking it. – cbunn Apr 12 '17 at 4:19
  • The class records should be stable. The schedules don't change very much. The only exception to this is that we have a few "flex" students who don't come on a set schedule, but rather they arrange times that fit both their own schedule and the school's schedule, usually on a week-by-week basis. This is something else I have to deal with, but at the moment I take your synchronization point. – cbunn Apr 12 '17 at 4:19
  • So, I think it might be best to go with Option 2, in particular the third point: to create them only as needed. I think this will reduce unforeseen foul-ups in linking classes to instances. You say this will make the SQL command for attendance registration more complex. I'm wondering if it's best to handle this within the database, via a trigger or stored procedure, or to deal with it using the application code (PHP)? – cbunn Apr 12 '17 at 4:20
  • @cbunn that would deserve a question on its own: both have advantages and disadvantages. The driving point is concurrency: what if 2 user register presence for the same course at the same time. Intuitively I'd go for the db stored procedure, because of the tighter coupling of related operations. But you should look at pros and cons in view of your specific larger architectural picture. – Christophe Apr 12 '17 at 8:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.