CODEWITHSUNDEEP
pythondjangodatabase
Hyperbola
Hyperbola

Reputation: 528

Database design and normalization in django

I have a web form where a student can enter their info and grades e.g.

This would transform to a POST request like below:

POST /grades
{
 "name": "Bob",
 "age": 17,
 "math_grade": "A",
 "spanish_grade": "B",
 "chemistry_grade": "C",
}

Finally, this would be stored in a SQL table grades like below by django ORM:

Name Age Subject Grade
Bob 17 Math A
Bob 17 Spanish B
Bob 17 Chemistry C

All good till here.

As I read about the different normal forms (1NF, 2NF, 3NF), it seems like the above table should really be two tables (student and grades) or maybe even three (student, subject DIMENSION table, grades FACT table).

Q1. If the grades table should really be split, should I do it at the application level where django ORM executes SQL query against multiple tables at once or am I pre-optimizing here? Should I do the normalization as part of my data warehousing strategy and leave the application table as-is?

Q2. If I want to return the grades for Bob in a nested format like below, how would I do that in django.

GET /grades/Bob
{
  "name": "Bob",
  "age": 17,
  "grades": {
     "math": "A",
     "spanish": "B",
     "chemistry": "C",
   }
}

These look like seemingly obvious questions, not sure if/what am I missing. Thank you!

Upvotes: 0

Views: 315

Answers (1)

NickW
NickW

Reputation: 9768

Dimensions and facts are types of table used in analytical (OLAP) systems are rarely in normalised forms.

If you are looking at creating a transactional (OLTP) system then it would usually be in a 3NF format. If the data you are showing is really all the information you have then 2 tables would be the best design: student and subject/grade.

In the real world you are likely to have much more information about a subject (level, duration, etc) so you would have a student table, a subject table and an intersection table holding the grade, date awarded, etc.

Upvotes: 1

Related Questions