CODEWITHSUNDEEP
mongodbnosql
Boy Pasmo
Boy Pasmo

Reputation: 8471

Thinking NoSql on reference data

I'm trying out NoSql and while exploring I can't get into my head on how to deal with reference data. (I'm used to traditional database, the tabular one) Say, I have a School entity which have Students and Requirements. Now, the Student can be enrolled to a School and may comply the Requirements later. So the School would look for a Student and check on which Requirements did he comply.

On traditional database, I would do something like.

+---------+  +---------------+ +--------------------+ +---------+
| School  |  | Requirement   | | StudentRequirement | | Student |
+---------+  +---------------+ +--------------------+ +---------+
| Id (PK) |  | Id (PK)       | | Id (PK)            | | Id (PK) |
| Name    |  | Name          | | StudentId (FK)     | | Name    |
+---------+  | SchoolId (FK) | | RequirementId (FK) | +---------+
             +---------------+ | HasComply          |
                               +--------------------+

I would create 4 Entities, and the Requirement has a many-to-many relationship to a Student. So whether I edit or remove a Requirement I can just look at the intermediary table.

A flow something like:

// EnrollStudentToASchool
// AssignAllRequirementsToNewStudent

Then somewhere in my code, if a new requirement was created

// IfNewRequirement
// AddNewRequirementToStudent

Now, in NoSql and in my case I'm using mongodb, a doc type data store. I read somewhere that data should be inline. Something like:

{
  Id: 1,
  School: 'Top1 Foo School',
  Requirements: 
  [
    { Id: 1, Name: 'Req1' },
    { Id: 2, Name: 'Req2' }
  ],
  Students: 
  [
    { 
      Id: 1, 
      Name: 'Student1',
      Requirements:
      [
        { Id: 1, Name: 'Req1', HasComply: false },
        { Id: 2, Name: 'Req2', HasComply: true },
      ]
    }
  ]
},
{
  Id: 2,
  School: 'Top1 Bar School',
  Requirements: [],
  Students: []
}

The root of my document will be the School, same flow above:

// EnrollStudentToASchool
// AssignAllRequirementsToNewStudent
// IfNewRequirement
// AddNewRequirementToStudent

But in case of, say, the School decided to edit the name of the Requirement or remove a Requirement.

How it should be done? Should I loop all my Students and Edit/Remove the Requirements? Or maybe I'm doing it all wrong.

Please advise.

Upvotes: 2

Views: 504

Answers (1)

profesor79
profesor79

Reputation: 9473

This a nice use case.

Your example brings up most of the relevant pros and cons about converting from sql to noSql.

First please see proposed collection design: collection

We have two collections: school and student why that? We need to think about bson document size limitation (16MB) and if we have a good school number of students could go over that size.

So why we duplicate data in every student record? If we want to have students details we don't need to go to school (no extra round trip).

We have array of requirements to fulfil in school (a kind of master), and then every student has its own array with result.

Adding / removing such data requires iteration via all students and school.

So in simply words - no join on daily display operations=> efficiency, but update generates a bit more load versus sql.

Any comments welcome!

Upvotes: 1

Related Questions