Real Estate Problem: Multiple agents per transaction and commission splits (help me!!)

Hi all! First time poster here. I have been working on this problem for a few days now, plus religiously combing through all these wonderful discussion posts; but still can't find my answer.

Here's my problem: I work for a real estate company and am tasked with designing our database. Right now I have two tables, one is Transactions and the other is Agents. Every transaction has at least one agent, but sometimes it has two.

• I want to be able to assign each transaction to two different agents, so that when I pull an agent, it will show each transaction they were involved with. 

• Each agent has a different split commission percentage. This means the total from the transaction is split differently for each agent in the transaction. I only want the split commission to be linked to each respective agent. So if Agent 1 is Jane Doe and Agent 2 is John Deer, I don't want Jane's commission to be linked to John and vice versa.

I got through the first step, but the second one really stumped me. I set up reference fields for each agent and while it would allow me to select two different agents in the ref fields, it wouldn't let me split up the fields per agent. I built a pipeline for it but it didn't work (because I kinda suck at pipelines lol).

Please please help, any advice is welcomed. Thank you so much in advance!

------------------------------
Karah W.
------------------------------