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
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.