Can this be done? and how many tables should I use?

The clients and products part is pretty straightforward:

You’ll need a table for each and a field in the Clients table that can link to multiple products.

However, tracking financial, rates, and relative dates can become very tricky. The required setup could depend on questions like:

Do your commission rates on a product ever change? If so, you’ll need to ensure historical data isn’t mutated when you update the rate.

Do your commission periods ever change? If so, ditto.

Are your commissions received at dates relative to individual clients, or relative to a product * number of clients on that product?

If a rate changes, does it change across all clients on the product?

I’m sure there are other variables I’m not even able to think of.

One note or suggestion I would add is to consider having a ‘Sales’ or similar table which basically is your ledger of transactions. For example, this would have a link to the client and the product sold to them and could have other information like cost, date, etc. But this way you have a ‘static’ record of the sale and product information, vs. just linking your products to clients.

This Airtable guide explains this better:

Thanks Jeremy. The commission part is pretty static. for the most part they are flat fee commissions that are paid either annually or monthly depending on the product and while they can change from year to year I’m just looking to have a place to go into record what I actually received for each client so that i can then run reports to see if i’m missing any payments that I should have recieved…

So when I put a field in the “Clients” table that can link to multiple products… how does that work… would I just call it the “Product” field in the clients table and then link it to the product table?

Thanks Alex… much appreciated. So if I did it how you are suggesting are you saying I would have a “sales” table in addition to a “products” table? or in place of a products table?

Thanks

Yes, my recommendation would be to have a ‘junction’ table between your Products and Clients, in this case called ‘Sales,’ which would act as a ledger of each individual Sale event with associated data.

As far as the commissions go though, that I am not sure of without more information / not sure I would be the best person to assist!

Hope this is helpful.

Blair,

I’m an insurance broker as well, I’m in group insurance. I am interested to see how this project has gone and to see if we can assist one another in building a table that could help others.