Table relational design (Buyers - Payments - Agents) (1 Viewer)

[moi]

Hello all,

Can someone please help me to create a table for entering and monitoring a commission for each agent from buyers.. I have tables of buyers, payments and agents.. i hardly create a table which field to include in table commission.. i am thinking to put the id's from buyers, payments and agents as FK.. what would be my form (mainform + subform)?.

Every time a buyer make a payment, corresponding agent will be entitled a commission.. then i need to make a total, displayed on form the total commission of that agent from that buyer..

But again, my problem is, agents are not limited to one buyer, they have many buyers, (one-to-many).

Please need help.

Thank you,

 

[MarkK]

Isn't the commission an attribute of the payment? Like, if you already have a payment table that connects buyer and agent, wouldn't it work well to record the commission in a field of the payment table? It occurs on the same date as the payment. It should be linked to the same buyer and agent as the payment. It is probably calculated as a percentage of the payment. Doesn't it belong in the payment table?

 

[plog]

1. How is the commission determined? Flat rate->e.g. $10 per purchase regardless of size? Percentage->%5 of purchase? Other?

2. At what level is the commission set? Is it universal no matter agent/buyer? Is it tied to the agent? Is it tied to the buyer? Can every agent/buyer comibnation have their own commission?

 

[moi]

Isn't the commission an attribute of the payment? Like, if you already have a payment table that connects buyer and agent, wouldn't it work well to record the commission in a field of the payment table? It occurs on the same date as the payment. It should be linked to the same buyer and agent as the payment. It is probably calculated as a percentage of the payment. Doesn't it belong in the payment table?

Hi I am not sure if it should be included in the payment table..

 

[moi]

1. How is the commission determined? Flat rate->e.g. $10 per purchase regardless of size? Percentage->%5 of purchase? Other?

2. At what level is the commission set? Is it universal no matter agent/buyer? Is it tied to the agent? Is it tied to the buyer? Can every agent/buyer comibnation have their own commission?

Hi,

1. It is a flat rate of 5% per buyer per lot..

2. Its the agent only.. Agents gets paid from every payment made by the buyer..

Thank you.

 

[plog]

There's no reason for the commission to be stored.

You build a query, use math in that query to calculate the commission then reference that query when you need the commission.

 

[moi]

There's no reason for the commission to be stored.

You build a query, use math in that query to calculate the commission then reference that query when you need the commission.

Thanks plog..

 

[MarkK]

There's no reason for the commission to be stored.

There is every reason to store the commission on the date it is incurred. with the payment or transaction on which it is calculated.
Any time a rate is subject to change, like tax or payroll or commission, the amounts calculated using today's rate should be stored in a record with today's date. You can build a system that doesn't abide by this rule of thumb, but then if a rate does change, it breaks your system.

 

[plog]

There is every reason

That's neither "every" (it's just one) nor "is" (it's not a situation in the present tense, its future imperfect) a reason to store this data.

If in the future the commission does become variable or changes at all, the cost/pain of switching over to a table system that does store it is very minimal--adding one field and running 1 UPDATE statement. Right now its not worth the cost/complexity of dealing with this hypothetical future issue. Use a query.

 

[Pat Hartman]

I would store the calculated commission. That way, you separate the calculation which may change over time from the result which is fixed based on a given payment for a given item on a given date.

The question to ask is, is it possible for multiple sales people to get commissions on one sale or payment? I've handled numerous commission applications and the answer is, in some cases yes. I had one that was for land sales (actually swampland in Florida). There were three parties.
1. the salesman
2. the manager
3. the regional manager

Each person had a different calculation and even different rules for having to pay back front money.

Other applications had one person per transaction but different types of people got different rates.

Commissions can be very complicated and you don't generally want to calculate them on the fly. Calculate them when the payment transaction is entered and store the result.

 

[moi]

I would store the calculated commission. That way, you separate the calculation which may change over time from the result which is fixed based on a given payment for a given item on a given date.

The question to ask is, is it possible for multiple sales people to get commissions on one sale or payment? I've handled numerous commission applications and the answer is, in some cases yes. I had one that was for land sales (actually swampland in Florida). There were three parties.
1. the salesman
2. the manager
3. the regional manager

Each person had a different calculation and even different rules for having to pay back front money.

Other applications had one person per transaction but different types of people got different rates.

Commissions can be very complicated and you don't generally want to calculate them on the fly. Calculate them when the payment transaction is entered and store the result.

Hi pat,

yes some buyer has more than 1 agents (2/3), but they were not paid individually, they get their commission as 1 group (they themselves will then divide).

Yes, i need to store the commission amount.