0

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.

1
  • All of these requirements are better implemented at the application level. The only harder consideration is the data structure for to account fro multilevel approvals and how that is modeled. Look as the SQL queries generated for your current structure and it might become more obvious how to model this different (if there is a need at all).
    – danblack
    Jan 13, 2022 at 3:58

1 Answer 1

-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.