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

Question

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.

Thanks.

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, Commented Jan 13, 2022 at 3:58

besar · Accepted Answer · 2022-01-21 10:45:27Z

-1

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

answered Jan 21, 2022 at 10:45

besar

1

Add a comment |

1 Answer 1