Single Table Inheritance

Single Table Inheritance (STI) is a design pattern in database modeling where a single table is used to store multiple types of entities that share a common set of attributes, but have some unique attributes as well. In other words, it's a way of organizing related objects into a single table, rather than having multiple tables for each type of object.

For example, consider a scenario where you want to store information about different types of vehicles (cars, trucks, motorcycles, etc.). All vehicles have common attributes such as make, model, and year, but they also have unique attributes such as number of wheels (for cars, trucks, and motorcycles) and bed size (for trucks).

Using STI, you would create a single "vehicles" table with columns for the common attributes and a "type" column to distinguish between the different types of vehicles. Each type of vehicle would then have its own unique set of columns for its specific attributes.

Here's an example of what the "vehicles" table could look like for this scenario:

Implementation With Sequelize.js

Approach I

Pros: single table holds attributes for each type

Cons: using the discriminator the option can add a performance overhead, as it requires Sequelize to query the database twice in order to retrieve the correct type of vehicle.

To implement Single Table Inheritance (STI) in Sequelize, you can use the discriminator property of a model definition. The discriminator property allows you to specify a column in the table that will be used to distinguish between the different sub-types of the model.

Here's an example of how you can implement STI in Sequelize for a scenario where you have a Vehicle model with sub-types of Car, Truck, and Bike:

const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'postgres',
});

const Vehicle = sequelize.define('Vehicle', {
  type: {
    type: DataTypes.STRING,
  },
  make: {
    type: DataTypes.STRING,
  },
  model: {
    type: DataTypes.STRING,
  },
  year: {
    type: DataTypes.INTEGER,
  },
  discriminator: 'type',
});

const Car = Vehicle.discriminator('car', {
  wheels: {
    type: DataTypes.INTEGER,
  },
});

const Truck = Vehicle.discriminator('truck', {
  bedSize: {
    type: DataTypes.FLOAT,
  },
});

const Bike = Vehicle.discriminator('bike', {
  engineSize: {
    type: DataTypes.FLOAT,
  },
});

With this code, Sequelize will automatically create a single table named "Vehicles" with columns for the common attributes and the sub-type discriminator column. The Car, Truck, and Bike models will then inherit from the Vehicle model and add their own unique attributes.

You can then interact with the STI models in the same way as any other Sequelize model, for example:

async function createVehicle(vehicleData) {
  const vehicle = await Vehicle.create(vehicleData);
  return vehicle;
}

async function getVehicles() {
  const vehicles = await Vehicle.findAll();
  return vehicles;
}

async function getVehicleById(id) {
  const vehicle = await Vehicle.findByPk(id);
  return vehicle;
}

And when you query the data, you can use the .instanceof method to check the type of a returned model, for example:

const vehicles = await getVehicles();
vehicles.forEach(vehicle => {
  if (vehicle.instanceof(Car)) {
    console.log(`${vehicle.make} ${vehicle.model} is a car with ${vehicle.wheels} wheels`);
  } else if (vehicle.instanceof(Truck)) {
    console.log(`${vehicle.make} ${vehicle.model} is a truck with a bed size of ${vehicle.bedSize}`);
  } else if (vehicle.instanceof(Bike)) {
    console.log(`${vehicle.make} ${vehicle.model} is a bike with an engine size of

Approach II

Pros: don't need to create separate models for each sub-type

Cons: you cannot add any additional attributes or methods to the Truck model.

const Vehicle = sequelize.define('Vehicle', {
  type: {
    type: DataTypes.STRING,
  },
  make: {
    type: DataTypes.STRING,
  },
  model: {
    type: DataTypes.STRING,
  },
  year: {
    type: DataTypes.INTEGER,
  },
});

const Truck = Vehicle.unscoped().filter({ type: 'truck' });

With this code, you can now interact with truck instances using the Truck model:

async function getTrucks() {
  const trucks = await Truck.findAll();
  return trucks;
}

Approach III

Pros: allowing you to interact with each vehicle type as a separate model

Cons: requires you to create and maintain separate models for each sub-type

An alternative approach to implementing STI in Sequelize is to add a type column to the Vehicle table and use unscoped filters to create separate instances of the model for each type of vehicle. Here's an example:

const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'postgres',
});

const Vehicle = sequelize.define('Vehicle', {
  type: {
    type: DataTypes.STRING,
  },
  make: {
    type: DataTypes.STRING,
  },
  model: {
    type: DataTypes.STRING,
  },
  year: {
    type: DataTypes.INTEGER,
  },
});

const Car = sequelize.define('Car', {
  wheels: {
    type: DataTypes.INTEGER,
  },
});

const Truck = sequelize.define('Truck', {
  bedSize: {
    type: DataTypes.FLOAT,
  },
});

const Bike = sequelize.define('Bike', {
  engineSize: {
    type: DataTypes.FLOAT,
  },
});

Vehicle.hasMany(Car, { as: 'cars', foreignKey: 'VehicleId', sourceKey: 'id', scopes: { type: 'car' } });
Vehicle.hasMany(Truck, { as: 'trucks', foreignKey: 'VehicleId', sourceKey: 'id', scopes: { type: 'truck' } });
Vehicle.hasMany(Bike, { as: 'bikes', foreignKey: 'VehicleId', sourceKey: 'id', scopes: { type: 'bike' } });

With this code, you can now access the different vehicle types using unscoped filters, for example:

async function getCars() {
  const cars = await Car.findAll({
    include: [{
      model: Vehicle,
      required: true,
    }],
    where: { type: 'car' },
  });
  return cars;
}

async function getTrucks() {
  const trucks = await Truck.findAll({
    include: [{
      model: Vehicle,
      required: true,
    }],
    where: { type: 'truck' },
  });
  return trucks;
}

async function getBikes() {
  const bikes = await Bike.findAll({
    include: [{
      model: Vehicle,
      required: true,
    }],
    where: { type: 'bike' },
  });
  return bikes;
}