Historical database design for school system

Question

Context

We are a multi-academy trust designing a database system (more of a warehousing solution) to aggregate educational data from our 13 schools into a single source.

The data will be mostly consumed in BI applications for visualizing and performing analysis.

One of the biggest hurdles we've had to overcome is retaining historical values for student attributes that change frequently. For example, it's reasonable to assume that for >99% of students, their year group will change every September (going up +1).

Immediately, we realised that this doesn't lend well to performing analysis on historical academic years.

Let's say that the lowest year group is year 8. We are unable to query any exclusions for year 8 students from last year (students that were in year 8 last year, not current year 8 students) because they are all* now in year 9.

*The vast majority are, at least.

Schema

db-fiddle for reference

Our solution was to design a schema like so:

CREATE TABLE student (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR,
  last_name VARCHAR,
  name VARCHAR GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);
 
CREATE TABLE student_year_group (
  id SERIAL PRIMARY KEY,
  student_id INTEGER REFERENCES student,
  year_group VARCHAR,
  as_of TIMESTAMPTZ
);

CREATE TABLE suspension (
  id SERIAL PRIMARY KEY,
  student_id INTEGER REFERENCES student,
  timestamp TIMESTAMPTZ,
  code VARCHAR
);

We then created a view for suspensions (prefixing all of our suspensions with e_ to mean "effective as of"):

CREATE VIEW e_suspension AS SELECT DISTINCT ON (id) id, timestamp, code, student_id, year_group_id FROM (
  SELECT
    suspension.id,
    timestamp,
    code,
    suspension.student_id,
    student_year_group.id AS year_group_id,
    as_of
  FROM suspension
  JOIN student_year_group ON suspension.student_id = student_year_group.student_id
) AS s WHERE as_of <= timestamp ORDER BY id, as_of DESC;

With the following seed data:

INSERT INTO student (first_name, last_name) VALUES ('Joe', 'Bloggs');

INSERT INTO student_year_group (student_id, year_group, as_of) VALUES (1, 'Year 07', '2020-09-01'), (1, 'Year 08', '2021-09-01');

INSERT INTO suspension (student_id, timestamp, code) VALUES (1, '2020-10-14', 'RESET Other'), (1, '2021-04-12', 'RESET Lesson'), (1, '2021-09-03', 'RESET Other');

We can now run queries like so and get back the following results:

-- We need to use the e_suspension view because we're querying student year groups
SELECT year_group, COUNT(*) FROM e_suspension
    JOIN student_year_group ON year_group_id = student_year_group.id
    GROUP BY year_group
    ORDER BY year_group;
-- year_group  count
-- Year 07     2
-- Year 08     1

-- We don't need to use the e_suspension view because we're not querying student year groups
SELECT name, COUNT(*) FROM suspension
    JOIN student ON student_id = student.id
    GROUP BY name;
-- name        count
-- Joe Bloggs  3

SELECT code, COUNT(*) FROM suspension
    GROUP BY code;
-- code          count
-- RESET Lesson  1
-- RESET Other   2

I'm aware that the student_year_group and suspension tables are not normalized. The student_year_group.year_group and suspension.code fields should be extracted into separate tables. In the context of this question, which is about tracking the historical attributes, I don't see any relevance in these tables being fully normalized or not.

Question 1 - Scaling to more student attributes

Aside from the general code review stuff, we are unsure of how well this will scale in regards to tracking more attributes. This demo just records year groups, but there are dozens of other student attributes that will need to be tracked the same way.

Also, as we increase the number of student attributes tracked, the complexity of the e_suspension view (and any other views that get developed at a later date, e.g. attendance marks, exam results, etc.) will rapidly increase.

Question 2 - Subquery performance

We're also curious about the performance of the subquery within the e_suspension view (and other views, see the above question).

Luckily, suspensions are not a frequent event; we expect this table to only reach a five digit record count. However, when we integrate attendance marks (an event that would require the same support for historical student attributes), we will be looking at a table and view with (easily) an eight digit record count.

Question 3 - Denormalized alternative

An alternative that we discovered during the design process was to denormalize the suspensions table and store the student year group (and any other student attributes that change over time) alongside each suspension.

This method increases the complexity of the initial table setup for each "student event", but allows us to do away with any views or subqueries/joins.

The downside to this method is that it becomes more tricky if we decide to add more student attributes to track in the future.

Question 4 - Historized student table alternative

Another alternative would be to make the student table historized, with all attributes (year group, etc.) included in the student table, and a new record for each time a student's attributes change:

student_id	first_name	last_name	year_group	as_of
1	Joe	Bloggs	Year 07	2020-09-01
1	Joe	Bloggs	Year 08	2021-09-01

This could potentially scale better with more student attributes, as it would just need an extra table column:

student_id	first_name	last_name	year_group	as_of	disadvantaged
1	Joe	Bloggs	Year 07	2020-09-01	0
1	Joe	Bloggs	Year 08	2021-09-01	0
1	Joe	Bloggs	Year 08	2021-10-14	1

However, with this schema, I can already see that if student attributes change frequently, a lot of duplicate data will be generated (in comparison to my current solution which is more "normalized").

Question 5 - Column-oriented DBMSes

Apologies if this sort of question is not in the spirit of code review.

Admittedly, I'm not too familiar with column-oriented databases, but a quick Google indicates that they might be well-suited to our use case?

We'd store all of the student data alongside a suspension, with no normalization, and then only select the columns (year group, other attributes, etc.) that we need.

Answer 1

for >99% of students, their year group will change every September (going up +1).

That suggests an alternative approach: instead of storing "year group", it may be better to store a "cohort identifier" (e.g. nominal intake year, or a September-based birth year) and map that to the year group. In September, the mapping base changes, but the only change to student data is to the cohort identifiers of the small number of students who repeat (or skip?) a year.

Answer 2

"Generic database" a.k.a. column-oriented

This is a comment, not an answer.

---

A cursory read of column-oriented sounds like what we called a generic DB. We built and maintained a generic database using MS/SQL. Note: I don't recall that performance was a particular issue...

Verbose queries - even the simplest, practical useful queries needed 2 or more joins. You get to the point of memorizing often used integer key values. Unintuitively, complexity didn't seem to compound exponentially with query length. Structure helped comprehension in chunks. So much of it was idiomatic (sub)structure like SELECT .. JOIN ON .. JOIN ON ... WHERE with many joins being those "automatic", simple link related tables.

Purple Haze - Query text volume exacerbated bad, lazy formatting and unwisely proscribed coding guidelines. Formatting was by far my biggest problem because it was so consistently non-existant. Once I spent 4 hours formatting (not RE-formatting!!) a query just so I could debug it.

Keys to success - Well designed compound keys are necessary and very useful. I found performance improved - at least was not hampered - if joining columns followed the order defined in the compound key. A one column join using a compound key had no performance drag if it was the 1st column in the key definition.

Mass Stupidity - Conversely, defining arbitrary table keys is a travesty. These things make rows unique without purpose and they do not define relationships. That id SERIAL PRIMARY KEY is one of these. This practice seems widespread to me; hunt down all who teach this to our impressionable programming progeny and administer a merciful frontal lobotomy. Better they than we; I'd rather have a bottle in front of me than have a frontal lobotomy.

Erudition - Define as many keys as are useful on a table. The maintenance overhead is richly compensated in the brazillion JOINs you will write. Study the DBMS documentation for subtle differences between PRIMARY KEY, UNIQUE, REFERENCES, et cetera for example.

Scale - Our generic database design scaled well. However, just as we know the Sun will someday explode, we saw that for one particular table we would one day hit the ( our current DB engine ) table size/row limit.