Relational Data Modeling: Multiple simple queries vs heavy query

Question

I am trying to determine the best approach to model data that is organized in and needs to be queried in groups and subgroups. Specifically, whether flattening/abstracting into one table and putting the work into the serving computation to filter and group query results (but only one query) will perform better than having separate queries that all need to be run at the same time but of course don't need to be grouped. for example imagine having to serve student test results by category to a dashboard.

approach 1. table for each test

   CREATE TABLE mathSkillsTest(
    id char,
    datetake date,
    multiplication_score integer,
    division_score integer,
    algebra_score integer
    student_id integer REFERENCES students (student_id)
  )
  ...create TABLE verbalSkillsTest// all scores still just integers
  ...create TABLE readingTest//  

advantages: Less abstraction easier to reason about, simpler queries, eg var mathscores = (SELECT * FROM mathSkillsTests WHERE student_id = 123)

disadvantage: If the plan is to always query all of the groups, that means we have query each table.

approach 2. One table

CREATE Table abstractTests (
  id char,
  datetake date,
  category varchar, //"math", 
  testname varchar, // "multiplication"
  score integer
  student_id integer REFERENCES students (student_id)
)

advantage: one table - one query.

disadvantage: lots of processing and grouping in the query to serve results grouped by category/test/date. also will have disparate data all on one table.

Is one approach more performant & scalable? are either of the approaches violating any best practices?

Answer 1

From a standard schema design approach, and for better maintainability and simplicity in my opinion, the second approach with a single more generalized table would be my pick. At the end of the day, your object here are tests. I believe you would find a lot of field redundancy in the first approach at an unnecessary attempt to improve performance.

If you'll generally be grouping and / or filtering on the same fields, such as category, testname, and datetake, then those likely would be good candidates for your index on the table which would allow utmost performance when querying it, even if your table grew large enough into the billions of records.

Answer 2

Analysis

may take 1 to n of the assessments

This indicates that you have a many-to-many relationship between students and assessments. (testname)

seeing all tests ever taken by a given student.

This is your primary SELECT statement.

Suggested Solution

Approach 2b

This is the same as Approach 2 but denormalizes testname and moves 'category' to a parent of the new TestNames table.

This adds the benefit of ensuring that rows cannot mix Math assessments with a Language category.

Dashboard Implementation

The database is best suited for returning the required data.

The display tier (eg web browser) is best suited for making it look pretty.

This concept is shown in almost all of the Dynamic Pivot questions here on SE and other places.

If you are going to show the data in 3x separate tables in a piivoted fashion such that the display looks like Approach 1, this method (Dynamic Pivot) is what will be suggested to you anyways.

With this method, you just send all 400+ rows of data for one student to the browser (usually in JSON format) and let the browser group it together and sort it out.