How to design a complex multi approval expense db schema in MySQL



  • I'm very beginner in database design and had the following problem to solve.

    DB schema design for an expense approval system with these cases

    • approval system, every company has 1 or many teams where rules can be different
    • let’s say for marketing team invoice needs to be approved by HR & CEO
    • while for engineering team, invoice needs to be approved sequentially by Engineering Manager to HR and last to CTO
    • there are also parameters that can define rule like amount of invoice
    • let’s say for invoice > 1000 USD, will need approval from CEO while invoice < 1000 USD doesn’t need it,
    • And for invoice category of office_supplies, need to be approved by GA
    • User should be able to query and get all the invoices required to be approved

    The schema must be dynamic and adaptable to future changes in business logic

    I have designed a simple schema as a starting point, please help in adding the approval rules.

    I would also appreciate any links or resources which could help me get started for problems like this.

    enter image description here

    Thanks.



  • Check out database triggers for defining dynamic rules: https://www.geeksforgeeks.org/sql-triggers/




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2