database

Sequelize Database Entity Relation for Postgres SQL-Many To Many Relation

In case of Many To Many relation we can define relation using Sequelize belongsToMany method. But before moving on to Many-To-Many relation in sequelize, I suggest readers to go through my previous post on One-To-Many relation with sequelize.

Defining one-to-many relation in Sequelize

Now let us consider the below relation for understanding on how to define Many-to-many relation using sequelize. If you are developing a platform for school management system and considering the requirement one user is associated with many schools, this is true in case of teachers, where they will be teaching in multiple schools / colleges as guest lectures.

The database schema for Users and Schools Entities looks as follows.

Defining Many-To-Many Relation using Sequelize

Users Model


const UserModel = db.define(
  'users',
  {
    userId: {
      field: 'userId',
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4
    },
    firstName: {
      field: 'firstName',
      type: sqlOperator.STRING(100),
      require: true
    },
    lastName: {
      field: 'lastName',
      type: sqlOperator.STRING(100),
    },
    email: {
      field: 'email',
      type: sqlOperator.STRING(255),
      defaultValue: null,
      allowNull: true,
      // unique: true,
      validate: {
        isEmail: true
      }
    },
    userName: {
      field: 'userName',
      type: sqlOperator.STRING(255),
      defaultValue: null,
      allowNull: true,
      // unique: true
    }
  },
  {
    tableName: 'users',
    timestamps: true,
    freezeTableName: true,
  }
);

UserModel.associate = async () => {
  //defining relation in user schools model
}

module.exports = UserModel;

Observe here that the relation between Users and Schools is defined through another intermediate table called User Schools hence the ralation is defined using belongsToMany using through keyword as given below.

UserSchools Model

const SchoolModel = require('./SchoolModel');
const UserModel = require('./UserModel');

const UserSchoolModel = db.define(
  'user_schools',
  {
    userSchoolId: {
      field: 'userSchoolId',
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4
    }
  },
  {
    tableName: 'user_schools',
    timestamps: true
  }
);

UserSchoolModel.associate = async () => {
  const UserModel = require("./UserModel");
  UserModel.belongsToMany(SchoolModel, {
    through: UserSchoolModel,
    foreignKey: "userUserId",
    as: 'userSchools'
  });
  SchoolModel.belongsToMany(UserModel, {
    through: UserSchoolModel,
    foreignKey: "schoolSchoolId",
    as: 'schoolUsers'
  });
};
module.exports = UserSchoolModel;

If you try to achieve this only using belongsTo and hasMany then you will face many sequelize errors while querying. You must use belongsToMany along with through keyword.

Scalability, architecture , Multitenancy : Database

Scalability, architecture , Multitenancy : Database

Assume that you have multiple clients, each client has access to similar kind of data, so database architecture should remain same for every client. 

An example for the same is , assume that you have developed an ERP product for a company, many companies will be having same requirement so the database will remain same.

Most of the developers will be under confusion, whether to go for single instance of database or multiple instance of database. Following are the two approaches and their pros and cons.

Approach 1 : Single instance database

  Keeping single instance of database but providing client id for each client to keep track of data of each client. 

Advantages : 

1) Better resource utilization – If your database is hosted on cloud services, like Amazon then as you create more number of instances they will allocate resources accordingly and charges will be applied for each instance. 
Example – check pricing for db instance in amazon
http://aws.amazon.com/rds/pricing/
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.DBInstanceClass.html
 

2) Scalability – Scalability is more easy since single instance is maintained.

Problems : 

1) Security issues – since every client’s data is in the same instance, developers should be wise enough to take care of showing data to appropriate users. Highest risk factor with single instance is security

2) Data loss – if the instance fails due to some problems related to one client, it affects to every other client, so routinely back of database is must be taken care. No compromise with data loss.

3) Performance issue – For a huge set of data performance of database might be low ( this matters for very huge data, not even for medium scale database )

This is called as Multitenancy – a principle in software architecture where a single instance of the software runs on a server, serving multiple tenants.

Approach 2 : Multiple instances database

with this approach all the problems of first approach will be solved but the only problem with this approach is scalability.