Database structure advice

Working with Data
1

Hi everyone. I needed some insight on how to structure a database for a project. The project is basically a school management application made using the mern stack. I have already inserted preexisting data for 1400 students. The students are divded in class prep, nursery, 1 - 10. Every class has 4 sections A to D. Every class has a particular fee associated with it. In the application i would want to add and remove students from a class also classs would change after a student successfully passes the session. I would want to gather and update fee payment history. I would want to calculate expenses and do some analytics about the budget. Focusing just on the fee collection how should I go about structing the database. Should i make a collection of classes with the relevant sections and fee. When the students pays the fee how would I go about storing the data should I create a hasPaid field that accepts a boolean for every student in the collection or make a field that holds an array of student fee information. How should I index the db without affecting performance.

Some insight on the matter would mean a lot for me.

Thanks in advance

3

Hey @Ateeb_Ahmed,

Welcome to the MongoDB Community Forums! :leaves:

A general rule of thumb while doing schema design in MongoDB is that you should design your database in a way that the most common queries can be satisfied by querying a single collection, even when this means that you will have some redundancy in your database. Thus, it may be beneficial to work from the required queries first, making it as simple as possible, and let the schema design follow the query pattern.

Based on what you described, one example design you can document your data is in the following manner (please test and alter according to your use case and requirements):
Student Collection:

{
  _id: ObjectId,
  name: String,
  class: String, // Class name (prep, nursery, 1-10)
  section: String, // Section (A, B, C, D)
  feePaid: Boolean,
  feePaymentHistory: [{
    date: Date,
    amount: Number
  }],
  // Other student information
}

Classes collection:

{
  _id: ObjectId,
  name: String, // Class name (prep, nursery, 1-10)
  sections: [String], // Sections (A, B, C, D)
  fee: Number, // Fee associated with the class
}

Coming to fee payment, one option is to set a feePaid field in the student document as a Boolean, indicating whether the fee has been paid or not. You can update this field to true when the fee is paid. Simultaneously, store fee payment information as an array of objects in the feePaymentHistory field of the student document. Each object can contain the date and amount of the fee payment. You can add a new object to this array whenever a fee payment is made. When the fee gets due, set the feePaid field to ‘false’.

I would suggest you use mgeneratejs to quickly create and test different design scenarios. This will also help you test will fields you should index on, to improve query performance. Additionally, for more advanced design patterns, you might want to have a look at building with patterns and see if any of the patterns might help your use case.

Hope this helps. Feel free to reach out for anything else as well.

Regards,
Satyam

4

Ty very much for you reply.

const classSchema = new mongoose.Schema({
  ClassName: String,
  Section: Array,
  fee: Number,
});


const employeeModel = new mongoose.Schema({
  first_name: {
    type: String,
    required: true,
  },
  last_name: {
    type: String,
    required: true,
  },
  gender: {
    type: String,
    enum: ["Male", "Female"],
    required: true,
  },
  type: {
    type: String,
    enum: ["Teaching", "Non Teaching"],
    required: true,
  },
  father_name: {
    type: String,
    // required: true,
  },
  address: {
    type: String,
    // required: true,
  },
  cnic: {
    type: Number,
  },
  phone: {
    type: Number,
    required: true,
  },
  dob: {
    type: Date,
    // required: true,
    // set: (val) => {
    //   const [day, month, year] = val.split("/");
    //   return new Date(year, month - 1, day);
    // },
  },
  last_qualification: {
    type: String,
  },
  passing_year: {
    type: Number,
  },
  marks_obtained: {
    type: String,
  },
  board_uni: {
    type: String,
  },
  designation: {
    type: String,
    required: true,
  },
  joining_date: {
    type: Date,

    // set: (val) => {
    //   const [day, month, year] = val.split("/");
    //   return new Date(year, month - 1, day);
    // },
  },
  package: {
    type: Number,
    required: true,
  },
  status: {
    isActive: {
      type: Boolean,
      default: true,
    },
    comments: {
      type: Array,
      default: [],
    },
  },
});


const PaymentSchema = new mongoose.Schema(
  {
    studentId: { type: String, required: true },
    ClassName: { type: String, required: true },
    Section: {
      type: String,
      enum: ["A", "B", "C", "D", "E"],
      required: true,
    },
    amount: Number,
    date: Date,
    payId: String,
  },
  { timestamps: true }
);


const studentMODEL = new mongoose.Schema({
  Name: {
    type: String,
    required: true,
  },
  DOB: {
    type: Date,
    required: true,
  },
  Gender: {
    type: String,
    required: true,
  },
  Father_Name: {
    type: String,
    required: true,
  },
  Phone_No: {
    type: String,
    required: true,
  },
  Address: {
    type: String,
    required: true,
  },
  ClassID: {
    type: String,
    required: true,
  },
  Section: {
    type: String,
    required: true,
  },
  createdAt: {
    type: Date,
  },
  status: {
    isActive: {
      type: Boolean,
      default: true,
    },
    comments: {
      type: Array,
      default: [],
    },
  },
});

I issue is that I have to run a lot of complex queries and my collections are very intertwined. For some reason my query time has shot up to unrealistic times especially for the students collection where I am housing 1400 documents. And if you look closely at payments I am making invoking functions to dynamically generate payID which I think is a great solution to restrict to the user for making duplicate entries for fee payment of a monthly fee payment twice. Again the price I am paying over here is with performance. Can you please guide me on what approach to take. I am thinking of implementing a service oriented architecture to have cacheing coupled with mongo to improve performance but the implementation Im am not very comfortable with. Also it has made me to rethink the api paradigm because using only the restful approach is making the code unmaintainable and libraries like graphql keep making breaking changes so its not a comfortable fall back.