Help building complex 'rate card' and commission calculation

Barney_Goodman · ‎May 26, 2020

Hi There,

Looking for a little help either building or advising on this build.

We are a credit broker B2B2C. I’m building an Airtable base to manage client onboarding, management and some internal BDM - CRM features. I need some help working out how to structure ‘rate cards’ per merchant (client) initially…

Each merchant has a different combination of lenders
Each merchant has a different combination of finance products to offer their customers from those lenders.
Each merchant may be charged a subsidy (or paid a commission) at different rates (depending on size etc) on the same product with the same lender
We may receive different commission rates from the lenders so need to be able to articulate this too.

The end goal here is to have a ‘rate card’ against each merchant record. I believe I need to look at the many-to-many links but just want some advice on how best to structure this.

Thanks in advance!

Kamille_Parks · ‎May 26, 2020

My first impression is that you may need a junction table to accomplish your many-to-many-to-many relationship.

Consider a base structure like this:

[Merchants] table, one row per merchant
[Lenders] table, one row per lender
[Finance Products] table, one row per product sold by each merchant through a particular lender. This would be the “junction table” which would have one Link to Another Record field for the merchant, another one for the lender, and a field describing what the product is.
[Rate Card] table, one row per rate of commission/subsidy of each merchant's products (this would allow you to store as many rates for a product as you’d like). This table would have a Link to Another Record field for the finance product to which the rate applies. If you have a Lookup field pointing to the [Finance Products] table at the {Merchant} field, you could use that field to group together all the rates by merchant.

Let me know if this answers your question. If it does, please mark it as the solution.

Barney_Goodman · ‎May 28, 2020

Hi @Kamille_Parks thanks for the suggestions, I may be being stupid but having difficulty pulling it all together. As you can see i have the Retailers (merchants), lenders, finance products and ratecard tables created with some example data in each albeit not quite right yet. Struggling to pull it all together as a unique ‘retailer rate card’ on the ‘Retailer’ table. I’m not sure if i’ve even got the ratecard table right. Apologies for my ignorance!

Kamille_Parks · ‎May 28, 2020

Did you mean to attach screenshots?