Database and model design - mongoose
Tags: javascript, mongodb, database, mongoose
Published: 2021-12-16 05:13:31

A new user Alan Tang, on Stackoverflow posted a question regarding the top-level design of schemas for database. 
The link to the question is here: 
[https://stackoverflow.com/questions/70345194/node-js-model-with-inner-nested-array/70345760#70345760](https://stackoverflow.com/questions/70345194/node-js-model-with-inner-nested-array/70345760#70345760)
More detail information is below:

 

> I have a question about my collection design.
 Current design


**Code (Part-I):**
{% runkit
%}
const customerSchema = mongoose.Schema({
customer_name: {
    type: String
},
purchase_history: [{
    amount: {
        type: Number,
        default: 0
    },
    currency: {
        type: String,
        require: true
    },
    description: {
        type: String
    }
}],
......
});

{% endrunkit %}

> Every time, if a customer purchases a new item, it will push the history into the "purchase_history".
The purpose of "purchase_history" is to let them check their own history.
Is this a good idea? or If you have a good idea, please few free to share.
Thank you

I **answered** to the above question like this:
 I have created 2 different schemas, one for the **customer** and another for the **purchase**. Please follow the standard procedure to keep all the **service** files, **model** files, **controller** files in **separate** folders.
The below is the **customer model**.

customer.model.js
**Code (Part-II):**
{% runkit
%}
const mongoose = require('mongoose');
let Schema = mongoose.Schema;

const CustomerSchema = mongoose.Schema({
  customer_name: {
    type: String,
  },
});

const customer = mongoose.model('customer', CustomerSchema);
module.exports = customer;
{% endrunkit %}

We have the **purchase model** as:

purchase.model.js
**Code (Part-III):**
{% runkit
%}
const mongoose = require('mongoose');
let Schema = mongoose.Schema;
const customer = require('./customer.model');

var purchaseSchema = new Schema(
  {
    customerId: { type: Schema.Types.ObjectId, ref: 'customer' },
    amount: {
      type: Number,
      default: 0,
    },
    currency: {
      type: String,
      required: true,
    },
    description: {
      type: String,
    },
  },
  { timestamps: true }
);

module.exports = mongoose.model('purchase', purchaseSchema);
{% endrunkit %}

Here, we can see the customer data is stored in customer collection and the purchase data stored in the purchase collection. Each purchase record has a reference field **'customerId'** which is the **customer's unique identitifer**. This field is defined in the purchase model. The customer's purchase history can be **fetched** by quering for the customerId field.
We can create an api for fetching customer's purchases as:

purchase.service.js
**Code (Part-IV):**
{% runkit
%}
const purchaseModel = require('./purchase.model');

module.exports.getByCustomerId = async (_customerId) => {
  try {
    const purchaseList = await purchaseModel.find({
      customerId: _customerId,
    });
    return purchaseList;
  } catch (err) {
    throw err.message;
  }
};
{% endrunkit %}


**DESIGN PRINCIPLE:**
The design principle followed here is to **avoid duplication** as suggested by a Senior developer. It is not **good practice** to **store** the **same value** in **different collections**, the purchase data being stored in the customer collection as in Alan’s schema.