help with setup of table

[andydeans]

Hi,

I was looking for some help with how i would go about setting up my commission table.

Basically i have currently:

Users Table
UserID
Username
Password
Email

Clients Table:
ClientId
Client_UserID
FirstName
LastName
Address
PostCode
Tel
Email

Insurance Table:
InsuranceID
Clients_ClientID
Provider
Commission
DatePaid

CarInsurance Table:
CarInsuranceID
Clients_ClientID
Provider
Commission
DatePaid

I want to setup a commission table to track commission due for a user and list the provider and client name.

I am not sure if i should have a UserID column in my insurance tables for this to work?

Could anyone advise on what my structure should be?

I need to query it by dates.

So in the website the user needs to be able to filter the results based on month and year.

So show how much they have made in 2010 or show how much they have made in just the month of April.

Also if possible show how much is due to come in.

So if the datepaid column is null then show the total commission due in, then once it is paid it will change to show paid.

Hope someone can help me with this, appreciate your help.

Many thanks

[rch1231]

Hi,

I was looking for some help with how i would go about setting up my commission table.

Basically i have currently:

Users Table
UserID
Username
Password
Email

Clients Table:
ClientId
Client_UserID
FirstName
LastName
Address
PostCode
Tel
Email

Insurance Table:
InsuranceID
Clients_ClientID
Provider
Commission
DatePaid

CarInsurance Table:
CarInsuranceID
Clients_ClientID
Provider
Commission
DatePaid

I want to setup a commission table to track commission due for a user and list the provider and client name.

I am not sure if i should have a UserID column in my insurance tables for this to work?

Could anyone advise on what my structure should be?

I need to query it by dates.

So in the website the user needs to be able to filter the results based on month and year.

So show how much they have made in 2010 or show how much they have made in just the month of April.

Also if possible show how much is due to come in.

So if the datepaid column is null then show the total commission due in, then once it is paid it will change to show paid.

Hope someone can help me with this, appreciate your help.

Many thanks

Hello,

This may not be all you need and is based on what I would do. Remember there is more than one way to solve the issue so review this with an open mind and adjust it as needed. It may be way more or not as much as you need.

Add the following fields to both of the insurance tables:

Policy Start date
Next Due date
Date of Last Payment
Date of Next payment
UserID
Total Premium

These additional fields should help you with several issues:

• Tracking commissions for policies that started during the year.
• Tracking policies that are past due.
• Tracking when someone pays more than one payment.
• Total payments vs. Commissions.

The make a payments file something like this:

Payment_ID
Payment_Date
Policy_ID
Method_Of_Payment (Check, Visa, MC, Cash)
Amount_Of_Payment
Late_Fees
Date_Posted_to_Account
Date_Posted_to_Commissions

And Commissions table something like this:

Commission_Payment_ID
Payment_ID (From payments table)
Policy_ID
UserID
Amount_Of_Commission
Date_Created
Date_Paid

This will allow you to track several things:

• Did they make a partial or more than one payment.
• If the check does not clear and is returned what to deduct from commissions.
• What records need to be posted to the account and update the due date.
• What records need to have a commissions record created to be able to pay commissions.
• What Commissions have been paid.
• Total income against late fees and commissions.

Remember this is my take on what I would do and you will probably want to adjust some or all of it. It is just based on problems I have had to adjust for. I hope it helps. One other thing to think about is are there any policies where more than one rep is paid a commission?

[andydeans]

Hi,

Thanks so much for the help, I think this will help me a great deal so thank you.

That is a good point regarding the commission.

How would I build in commission splits then between reps?

Thank you again

[rch1231]

Hello,

The answer to that generates a couple of new questions.
What is the maximum number of Reps that share a commission?
How is commission determined (flat rate, percentage of total, etc.)?

Normally in SQL when you have multiple values you spin off a new table and link it back with a record ID. If it is NEVER going to be more than 2 Reps you could create 2 sets of fields in the Policy table to hold the data but it is not the best method. You now have to code to check both fields and output for each. It is better to make a new table. You could decrease the number of tables by combining the Policy tables into one and adding a policy type flag.

[andydeans]

Hi thanks again for the help.

I would not imagine it would be anymore than 3 reps.

and preferable having the option for percentage of total or flat rate would be good. would the be added into my "insurance" table?

or should i create a new table?

Thank you again

[rch1231]

I would drop the Commission field from the Policy table(s) and add a new table called Policy_Reps_Comm:

PRC_ID
Policy_ID
UserID
Date_Started
Date_Ended
Commission_Percent_Of_Total

On the last field it could also be commission_amount instead of a percent of the total.

Splitting the data out into a separate table you can handle things like:
Record 1 rep AAA gets 2.5% on policy 1234's Total_Premium
Record 2 rep BBB gets 2.5% on policy 1234's Total_Premium
Record 3 rep CCC gets 5% on policy 1234's Total_Premium because Rep AAA and BBB work for him.

By having a start and end date and creating a new record if the commission amount changes you have enough data to be able to recreate commissions. It gives a history of Rep AAA was paid r% from this date to this date and s% from this date to this date and t% since then. By having one rep per record you don't have to check for the other fields being filled in when doing your calculations and can make one pass through the table. It will make a lot of things easier in the long run, trust me.

You will be able to create queries that link all of the data together at the time you need it and hopefully very few holes to try and fill in later.

[andydeans]

rch1231 thanks so much i think your help is great!!!

One thing if you dont mind how would i do my queries?

At moment i have been using dreamweaver with php/mysql and creating my recordsets in dreamweaver from the mysql database and displaying and using dreamweaver to do the inserts and display the data in a table.

I have been able to simple queries and have been managed to do a few join queries with help from other people along the way.

However i am not sure how i would do these queries to display by month, year and how much due??

Could you help please point me in the right direction or advise where i should do the queries, database or app??

Thank you again

[rch1231]

rch1231 thanks so much i think your help is great!!!

One thing if you dont mind how would i do my queries?

At moment i have been using dreamweaver with php/mysql and creating my recordsets in dreamweaver from the mysql database and displaying and using dreamweaver to do the inserts and display the data in a table.

I have been able to simple queries and have been managed to do a few join queries with help from other people along the way.

However i am not sure how i would do these queries to display by month, year and how much due??

Could you help please point me in the right direction or advise where i should do the queries, database or app??

Thank you again

You need a real query editor to create them with. You can get one for free from http://dev.mysql.com that will allow you to connect to the database and help you create the queries. I have a well worn copy of O'Reilly's MYSQL cookbook that has been great.