Storing commission splits in DB and insuring database integrity

Here's the background:

Sales Agents sell insurance. They work alone or in an agency of 2 or more. When multiple agents work together, they need to split their commissions. The commission splits are predefined by percentages.

Example

Joe, John, and Saul belong to an agency.

For every policy sold Joe gets 40% of the commision, John gets 35%, Saul get 25%.

Every agent either works solo or is a member of one (and only one) Agency.

The obvious way to model this in the database is 3 tables: An Agents table, An Agency table and a AgencyCommissionSplits Table.

Agents table:

AgentID int PK
AgentName vchar(30),
AgencyID int FK Nullable

Agency Table:

AgencyID int PK,
AgencyName vchar(30)

CommissionSplits table:

AgencyID int FK,
AgentID int FK,
Percentage dec(3,1)
(compound PK AgencyID, AgentID)

The problem I see is I don't know how to insure that the percent totals for any given agency always totals 100%. I can try an enforce this in the front end, but I would really prefer a way in the database to enforce this constraint.

This is a classic chicken-or-the-egg scenario regarding database constraints. While the immediate option seems like writing a check constraint, this isn't practical since the data has to be in the table before the constraint would be able to validate it. If you're creating a new agency, then you're going to be inserting new rows, putting your table (temporarily) in a state where the percentages don't add up to 100%.

Your best bet is to enforce this on the application level; any database solution (should one even exist) is likely to be a fair amount of hackery (though I'd be happy to be proven wrong on this).

Are you familiar with Check constraints (in SQL Server)? They are quite flexible and can be used to enforce the situation you describe here.

To my mind the model is too simplistic but it depends on the complexity of the agencies / setup.

'Agent Joe' could leave the agency and start employment at another, but ongoing-commission from his previous agency must still be linked to him. He is a member of 1 and only 1 agency at a given point in time (obeying your rule), but the database might need to be historically accurate to handle commissions that pay over time.

As they replaced 'Joe' with 'Bill' the percentages of all the agents against that agency would then exceed 100% again unless the date was considered.

I would also not rule out that as an agency got larger, they were permitted far more combinations, so that 10 sales people did not automatically get a percentage on every policy, but they indicate who sold the product and what the respective commissions are.

In terms of your specific design, Agent.AgencyID can be nullabe, but the commission split requires both Agent and AgencyID, so unless you consider the lack of commission split record = 100% then it is going to cause you problems.

This is the kind of thing best done in a trigger if you need it to be in the database. But it will be complicated.

However. Part of your problem is that the first person who is entered in the database (before you enter the other two who will do the split) will not add up to 100 unless the first person is alawys made to be 100. Then how do you determine what to do when the update adds up to more than 100 or less than 100? who do you cut a percentage from or who do you add it to if the numbers don't add up to 100%?

Might be better to just mark records as not adding to 100% and refusing to apy commisions until the problem is manually cleared up.

"I can try an enforce this in the front end, but I would really prefer a way in the database to enforce this constraint."

The only existing DBMS that can do this for you, is my own : http://shark.armchair.mb.ca/~erwin

Constraining aggregations (and doing it at the highest possible level of efficiency/performance) is (and I'm putting it mildly) one of the more trickier problems in constraint enforcement, and there is not a single person in the world except I who knows how to do it.

You can believe me or not, you can find me arrogant or not, but what I'm telling you is that you won't be getting "a way in the database to enforce this constraint" from any of the major DBMS vendors in any of the earliest decades to come. They don't have a clue and it is even a fact that they don't really care about this problem.

If you're stuck with the crap DBMSs from the majors, read the other solutions and implement something like "just mark records as not adding to 100% and refusing to apy commisions until the problem is manually cleared up.".