Sto costruendo un'applicazione web in PHP per gestire il monitoraggio delle presenze nella mia piccola scuola inglese. Ho il database installato e funzionante con PostgreSQL e ho importato i dati e elaborato l'interfaccia utente per l'aggiunta di dati. Sono ancora un principiante con questo genere di cose, ma penso di aver pianificato bene il database.
Ma un problema mi sta facendo meravigliare. Il modo in cui ho costruito il database, ogni istanza di una classe (come nel record di una classe pianificata regolarmente in un particolare giorno) viene utilizzata per collegare i dati di presenza (presenza degli studenti insieme al feedback) alle classi stesse (es. il programma). Quello che mi chiedo è a che punto dovrei creare ciascuno di questi record di istanze di classe. Una parte di me pensa che dovrei avere una sceneggiatura per crearli in anticipo per tutto il mese o giù di lì. Ma l'altra parte di me pensa che dovrebbero essere creati su richiesta ogni volta che i dati vengono immessi richiedendo l'uso di un record di istanza di classe (ad esempio i dati di presenza).
Se creo i record in anticipo, snellisce il processo di collegamento delle lezioni di "trucco" dalla lezione persa alla classe di trucco. Ma potrei comunque dover creare alcuni dischi su richiesta se quella classe di trucco è più lontana nel futuro di quanto non abbia tenuto conto.
Se li creo su richiesta, mi sembra il più efficiente per il database, ma crea più lavoro per ogni punto di inserimento dati.
Sto facendo qualcosa di stupido qui? Ci sto pensando troppo? Se c'è un altro modo migliore di fare le cose, sono aperto ai suggerimenti.
Per riferimento, puoi consultare lo diagramma del database completo (Non posso ancora postare foto così come sono nuovo a questa sezione di StackExchange). Per quanto riguarda lo stesso SQL, ecco il codice:
-- 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
;