Wolfdog Wolfdog - 3 years ago 152
MySQL Question

SQL relations in NoSQL Schemas

So I need to design NoSQL Schemas (Mongoose) from next SQL Database.
Image of SQL database

So I have a few questions about how to make those Schemas:

  1. There is a field in table course_student called entryDate. In what Schema do I store this field and how do I connect it to other? (Refers to entry Date of one student to specific course) (Also the case for startDate in course_teacher table)

  2. In Admin, Teacher and Student Schemas, how do I inherit all properties from PersonSchema (is this the case for Discrimantors?), or do I store all of their fields in PersonSchema (the case I don't like, because in case of "admin", teacher and student fields would be all NULL)

  3. (Optional) Also, how to handle non-normalized case of many-many relationship? (Let's say there is no course_teacher table, and teachers & courses are connected with many-many relation)

Answer Source

In Mongo, like in SQL, there are many ways you can structure your data. The key to consider in Mongo, is how do you need to access the data. Try to group data in ways that it matches queries you expect to perform. Using that context, I'll try to answer some of your specific questions:

1) Your asking where to store the entryDate of a student into a class. Do you often pull students independently? Or do you always grab them with their classes? Do you need classes independently?

One option would be to nest classes inside of your students: Student =>

   firstName: "John",
   lastName: "Smith",
   classes: [{
      classCode: "EN101",
      entryDate: "10/10/2017"

You could also have a class object with a list of students if you access it all it once usually: Class =>

   classCode: "En101"
   students: [{
      studentId: 12345,
      firstName: "John",
      lastName: "Smith,
      entryDate: "10/10/2017"

If you often reference things separately, you could have a flat table like in your SQL that just has classCode, studentId, studentEntryDate

  1. These objects are all stored together in one table? If so that sounds like a great use of discriminators. like if you just have a People collection. However, I would guess that they are stored separately? Teachers and Students are stored in different collections? In that case you can just have them inherit the same class in code, and they will serialize fine into mongo without any discriminators.

  2. There's multiple ways to handle this. You could create collections for each table and maintain a linking table, but not usually the best way. I would think Teachers and Courses would be one-many not many-many? So each course would have a teacher. If courses do have many teachers, you can just have a list of teachers instead of a field called teacher.

Hope my answers are clear, feel free to comment for more information.

If your pretty new in Mongo, I recommend signing up for some of there free courses. There pretty useful and get you a good foundation.

Good luck on your switch to mongo. It's worth it!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download