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

[Blair_Jensen]

Hello, I am an insurance broker and I’m trying to create a client management database that will track my clients and the products I have sold them and also track my commissions as they are paid to me by the various insurance companies.

So there are basically client info, insurance products they purchased, and commissions paid to me that I’m trying to track.

So each client can have multiple products over the course of several years, and each product pays commissions at various times. Some products pay commissions annually, and some pay monthly.

I want to be able to pull up a clients info and be able to see, over the course of 10+ years or so what products they have purchased and each commission payment paid to me at specified dates.

Can this be done in airtable and how many tables should I create and how should I link them?

thanks in advance for any help!

[Jeremy_Oglesby]

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.

[Alex_Wolfe]

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:

Airtable - Support

A beginner's guide to many-to-many relationships

What sets Airtable apart from ordinary spreadsheet applications is its ability to link related concepts together. Knowing how to represent the relationships between different groups of objects can ...

[Blair_Jensen]

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?

[Blair_Jensen]

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

[Alex_Wolfe]

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.

[Jeremy_Day]

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.