I’ll give a quick rundown of what I’m trying to build, and then explain the trouble I’m having. I want to say I’m VERY early on in this, so I’m not married to anything I’ve done up to this point. If there is a better way of handling any part of this project, feel free to share even if it isn’t directly related to the issue at hand!

Basically, some employees at my company want an internal tool with a way to “approve” new purchases before we expense them to the proposed departments. So someone will submit a purchase order for approval, and then anywhere from 1-5 people would have to approve it, depending on the budget accounts involved.

Right now, I have a very simple Postgres DB spun up with just three tables: users, approvals, and user_approvals. The users table contains the 5 employees who need to approve orders, the approvals table contains the purchase orders themselves, and the user_approvals table is just the way that I’m keeping track of which users need to be approve a given proposal.

Let’s say that the approval with an id of 1 needs to be approved by employees with ids 3 and 4. The user_approvals table would have two entries–(approval_id: 1, user_id: 3), and (approval_id: 1, user_id: 4). I hope I’ve been clear up to this point, but if any clarification is needed please let me know! Or if there’s a better way of handling which users have access to which approvals, I’m always open to input.

Right now when user 3 looks at the web page that contains the purchase orders they need to review, I pull the relevant POs using this query:

SELECT * FROM approvals WHERE id IN (SELECT approval_id FROM user_approvals WHERE user_id = 3)

And this works. I grab the relevant info from the DB entry and lay it out on the page. It also ensures that no employee sees any purchase orders that don’t require their approval. However, I would like for user 3 to ALSO be able to see if user 4 has approved the purchase order yet.

In other words, I want each user to see the POs that involve them, as well as the responses from anyone else that’s involved. I don’t mind making two separate queries if needed. Should I just follow up with something like:

SELECT status FROM user_approvals WHERE approval_id IN (SELECT id FROM approvals WHERE id IN (SELECT approval_id FROM user_approvals WHERE user_id = 3))

This seems to work at least in this limited case, but also seems a little redundant lol. Any thoughts are welcome!