Best Database Structure

I’m curious the best way to structure my database - I’m sure the key comes down to what I’m searching for.

Let’s assume I want to store students grades across university subjects:

  • There are hundreds of subjects and students, but only a handful of different grades
  • New students are always being added
  • Subjects are updated occasaionally, but not that often
  • The aim is to be able to search the database for a number of subjects and then rank the students. e.g I might want to rank them across Science, Maths and Chemisty.

Four main ways I could do it are:

1. One record per student per subject

Student Subject Score
Student 1 English A
Student 1 Maths C
Student 1 Science A
Student 1 Chemistry D
Student 2 English B
Student 2 Maths C
Student 2 Science B
Student 2 Chemistry B

I see this as being the simplest, but will result in thousands of records, which could have an impact on searching speed? This is how I have it currently.

2. One record for every subject, with multiple students per grade field

Subject A B C D E
English Student 1 Student 2
Maths Student 1,
Student 2
Science Student 1 Student 2
Chemistry Student 2 Student 1

This feels more efficient, given the search criteria will be subjects, but I feel it could become convoluted once you get a lot of students in each field. This DB would only be as long as the number of subjects.

3. One record per student with multiple subjects per grade

Student A B C D E
Student 1 English,
Science
Maths Chemistry
Student 2 English,
Science,
Chemistry
Maths

This one feels pretty good from a record keeping perspective, as the student is essentially the primary field. However, values will rarely change per student and I’m more interested in the aforementioned speed which it might not do very well as it needs to look in every cell.

4. One record per student with a column per subject

Student English Maths Science Chemistry
Student 1 A C A D
Student 2 B C B B

I feel this is the most efficient when it comes to managing and searching data, but would be an absolute nightmare when it comes to adding and removing subjects, sepecially when there will be hundreds

for me 3rd option looking good.

the grades & subject you can put in the option set field as this is not going to be changed or rarely changed and it is going to live at client side and not in the server side,

This is a textbook many-to-many relationship.
You could read up on junction tables (not only on bubble, this applies to all database structures)

Option 1 is the textbook solution and is a general allrounder. You might worry about search efficinecy but Bubble (+ most other postgresql/sql based databases) will apply indexing to your table so that searches will actually be quite quick both on the subject field and on the user field. This is generally the best solution.

Scrap options 2 & 4.
Option 3 could potentially work if the grades which could be achieved across all subjects is standardised. However list fields are generally not great, and a nightmare to filter by/search on. You wouldnt be able to search for all students in english, you would need a supporting table. I imagine you might have another table which holds the student-subject relationship, so maybe that might not be a problem. However this solution is not very flexible.

I would go with option 1.
If you want to have a quickly accessible list of user subjects/grades, create a field on the user which lists the users’s grades. This would be a list of Option 1 records.

Thanks Nico!

I definitely felt like this was the best option, but having someone who knows more about DB structures than me is a great help!

For your last comment, are you saying that I could have another field on the user table (or other table linked to the user), which would look similar to the following (assuming UUIDs for the records in example 1 are just numbered sequentially from 1 to 8):

Student Grade Records
Student 1 1, 2, 3, 4
Student 2 5, 6, 7, 8