0

I am creating an Invoicing Application and I am using sequelize.

I want the invoice code (or id) to be sequential per (end-) user profile; not throughout the system/database.

For instance, let us consider 2 end-users who created 2 invoices. The first 2 distinct invoices for each end-users will be <0001> and <0002>; 4 invoices in total.

I am using MySQL where I can easily define an auto-incremental / sequential ID as so:

module.exports = (sequelize, Sequelize) => {
const Invoice = sequelize.define("invoice", {
id: {
  type: Sequelize.INTEGER,
  primaryKey: true,
  allowNull: false,
},
userId: {
  type: DataTypes.BIGINT,
  allowNull: false,
  references: {
    model: 'user',
    key: 'id',
  },
},
contactId: {
  type: DataTypes.BIGINT,
  allowNull: false,
  references: {
    model: 'contact',
    key: 'id',
  },
},
dueDays: {
  type: DataTypes.BIGINT,
  allowNull: false,
},
subTotal: {
  type: DataTypes.DECIMAL,
  allowNull: true,
}, ...

However, with the code above, an invoice will have a sequential/incremental id throughout the database regardless of the specific end user issuing the invoice.

What is the correct way to implement this so that the invoice ID is incremented per end user?

Should each user have its own database (a database dedicated to one end-user) accessible through one server instance?

Thanks!

Monero Jeanniton
  • 441
  • 8
  • 20
  • *"What is the correct way to implement this so that the invoice ID is incremented per end user?"* - is this `invoice ID` refers to the `primary key`? if so, i think you should put invoice ID on another column independent from the actual primary key. and increment the number manually from the app not using the database's auto increment. – Bagus Tesa Jan 08 '23 at 06:24
  • Hi @Ba I wanted the ORM, Serquelize, to generate the IDs for me so I (my algorithm) don't run into a collision; 2 or more invoices having the same id. I also wanted the know the best practice out there. Thank you for your answer. PS: Please, check this reference to apply constrain to an id: https://stackoverflow.com/questions/60526982/invoices-have-the-same-number-when-generated-at-the-same-time-in-node-js-how-to – Monero Jeanniton Jan 08 '23 at 07:32

0 Answers0