Help getting started please!!! 2

[MarkRCC]

Hi.

I've been assigned the task to do the following and hope you fine folks could give me a few ideas on how to set up my tables, etc.

Task:
Create an access database program that would track the commissions of approximately 1000 independent brokers on a monthly and YTD basis. These brokers have the ability to sell either traditional insurance (life, health, etc) or "money" (mutual funds, bonds, etc) with approximately 20 different companies. The commissions are calculated depending on criteria for each company. Thus, the database needs to keep separate track of 2 distinct commissions (traditional or money)each month and with a Year-To-Date (running total) with the 20 different companies. I hope this is making sense.

What I have so far:
I created 3 tables (so far):
tblBrokerInfo (individ. broker info)
BrkrInsCompanyName (field to link tblCompanyInfo table)
BrkrID (primary key linking to tblBrkrLifeMoneySales)
BrkrLN (broker's last name)
BrkrFN (broker's first name)
BrkrCompanyName (company name if no individual broker
is assigned)
BrkrOffLoc (location of broker's office)
BrkrFYCRate (broker's commission rate (entered by end-
user as a $$ amount and used in the calc-
lation of the broker's bonus). This #
may change each month.)
BrkrORRateA (number (expressed as a %) that's used in
the broker's bonus calculation))
BrkrORRateIA (same as above except used by one of the
20 companies))

tblBrkrLifeMoneySales (holds commission data for each
broker)
ORLifeSalesID (Links this table with tblBrokerInfo)
JanLSAmt - DecLSAmt (these fields hold each months
traditional insurance sales for
each broker)
JanMSAmt - DecMSAmt (same as above but for $$ sales)
LSAmtYTD (used for Year-To-Date trad. ins sales data)
MSAmtYTD (used for Year-To_Date $$ sales data)

tblCompanyInfo (holds ins. company info)
CmpnyName (name of insurance company)
TotalORRateLifeSales (used to store a number (expressed
as a %) that is a base percentage
that our company uses as a base
to calculate broker commissions
for that company.))
TotalORRateMoneySales (same as above, but used for $$
sales)

The formulas for the monthly commission calculations for each broker would be as follows:

traditional insurance sales:
FYC(BrokerInfo.brkrFYCRate)
*
Broker% (BrokerInfo.brkrORRateA) or (brkrORRateIA)

money insurance sales:
FYC * broker% (see above) OR an actual amount
that is entered by
end-user

The results would be saved in the corresponding LS or MS field in tblBrkrLifeMoneySales table depending on the month and also added to each corresponding YTD field.

Am I attacking this correctly? Any suggestions or ideas on how to do it better or more efficiently? Did I make any sense at all? (laughing).

Many, Many TIAs in advance!!

Mark

 

[lespaul]

Well at least you are asking about design issues before you start adding data and trying to run queries!

What you need to look into is Database Normalization. Here's a link (thanks to JeremyNYC) that explains this process and how to do it. I mention this because you have a glaring example of a denormalized table right here:

JanLSAmt - DecLSAmt (these fields hold each months
traditional insurance sales for
each broker)
JanMSAmt - DecMSAmt (same as above but for $$ sales)

(not to be critical, it happens alot around here!)

by structuring like this you are going to have troubles later on when you start wanting to run your reports.

So, here's the link:

http://www.alphabetcitydataworks.com/Developers/FundamentalsOfRelationalDatabaseDesign.doc

But, you are on the right track. Once you review this information, why don't you re-post with updated table information and then we'll review it again.




Leslie

 

[MarkRCC]

Hi Leslie.

Thanks for the rapid response. I'll check out the link right away.

Mark

 

[willir]

Hi Leslie -- you are hot today.

Mark...

After reviewing Normalization - a design process to remove unneccessary information, you will have some thoughts to consider. (Please, please, read up on Normalization)


You have some givens...

tblBroker
BrokerID - primary key for broker, autonumber should be okay, or a code
BrkrLN (broker's last name)
BrkrFN (broker's first name)
+ info on the broker


tblCompany
CompanyID - primary key for company, company code may be better but autonumber would work too.
CmpnyName (name of insurance company)
+ other info for Company, eg Address

First question(s)...
What is the relationship between the broker and the company? Does the broker work for the company, can a broker work for several companies, if he works for one company can he sell for another company???

This will decide whether you place the CompnayID in the Broker table, or use a separate table, tblBrokerCompany.


Next, centralize the insurence and funds. You can do this with one or two tables.

tblFund (for both insurance and funds)
FundID - primary key
FundName
CompanyID - foreign key
+ other stuff unique to the fund or insurance.

Or create tblFund and tblInsurence if the properties of each are too different.

Note that I have placed the CompayID on the fund table. I am assuming that a company will have their own fund or insurance policy - different name, different scope, etc. This is part of the normalization process where instead of using the company name on the fund, it links the fund to the comapny via the foreigh key, specifically, the CompanyID in this case.

This is also a one-to-many relationship -- One comapnay will sell several funds and insurance policies.

Next question(s)
Will a company always pay X% for insurance and Y% for funds for commision, or does this depend on the specific fund or insurance? Will a compmany every promote a fund or policy by offerring a larger commission? If you can say that the commission rate will always be the same, and never, ever change, then you can put the commission rate on the Company table for the fund. -- OTHERWISE, put the commision rate on the fund table.

You may even have to create a FundYear table and place it here if the rates change over the years.


Next issue, commissions for sales.
Normalization would suggest that since some of these numbers are calculated fields, calculate the number each time rather than store the number.

However, you are talking money, and this may not be the best solution. Regardless, you still need to track transactions...

tblTransaction - tracks transactions, either detail or summary. This is one possibility; other designs may be better depending on your needs...

TransID - primary key
BrokerID - foreign key to broker table
FundID - foreign key to fund
(InsuranceID - foreign key to Insurance table if you decide to split fund and insurance)
TransDate - date field, can be for detail transactions or summary monthly transaction
TransAmount - currancy field for the transaction
+ other characteristics for the transaction - memo field, etc

As will be explained shortly, commissions probably should not be part of the transaction table. However, after reviewing your needs, you may decide to place the commission rate on the transaction table. [blue]But this should be an informed decision.[/blue]


I see a possible problem that you have not identified yet. We are tracking commissions, say for 2004. What happens in 2005?? What happens if rates change?

Your answers will dictate which direction to head. Here is one possible alternative...

tblCommission
FundID
CDate
CommissionRate

or...
FundID
CYear
CommissionRate

Primary key would be the FundID + the CDate or CYear. Useing the date format,CDate, would allow you to enter a change in the commission rate at any time. Your logic would grab the last value. The CYear would allow a commission to be entered once a year.


tblCommissionSum
BrokerID
FundID
CYear
Commission

(Primary key can the BrokerID + FundID + cYear, or use an autonumber field)

In a sense, this last table does not follow the normalization process. However, it will allow you to quickly review commissions without having to recalculate past values.

By the way, when you follow the link to JermeyNYC web page and read up on Normalization, you will read about many-to-many relationships.

This is a M:M
One broker can sell many funds
One fund can be sold by different brokers.
The suggestion for a Commission table is your joining table linking the two.


There are other ways to do this; this is just a suggestion to get the brain juices working after you read up on normalization.


Richard

 

[lespaul]

I may be hot, but you're SMOKIN'!!!

Nice description on the thought processes ONCE AGAIN! Have a star!

I'm actually just hanging out waiting for someone like you to answer MY question (Thread701-676215)

Have a great day!

Leslie

 

[willir]

Thanks for the star Leslie.

I will ponder your issue. (But my lunch is over) A new guy on the block, Golom rose pretty fast, and he is smack on with the SQL stuff - he makes some tough issues look amazingly simple.

Richard

 

[Golom]

Comes from being amazingly simple my own self.

 

[MarkRCC]

Thanks for the link to the "Fundamentals of Relational Database Design" paper. It was very eye-opening to say the least.

To answer willir's questions (I hope), this is what's needed in this particular program.

There's approximately 1000 independent brokers who sell either traditional insurance (life, health, etc) OR "money" stuff (mutual funds, stocks, etc) for (as of now) approximately 20 different insurance companies. Our company acts as a middle-man between the independent brokers and the insurance companies. We have to calculate each broker's commission based on the amount of the sale (the broker's FYC, which would be entered for each broker each time there's a sale) and the broker's commission percentage rate (For traditional insurance this is a static rate that changes relatively infrequently but for "money" sales, this number is either calculated using the same formula as the traditional insurance OR is an amount that has to be entered by an end-user). Our company get's a cut of the total commission using a "total bonus rate" (this is a percentage as well). Both types of "sales" (traditional and money sales) have different rate calculations.

For example,

Broker John Doe sells a life insurance policy this month (to a client while representing insurance company A resulting in $1000.00 in premiums) and shares of a hot stock (to a client while representing insurance company B resulting in premiums of another $1000.00). John Doe's FYC (First Year Commission - his base commission)is $100.00. (an amount determined by our company) John Doe's commission would be calculated in the following manner:

Company A: Sells $1000.00 in life insurance premiums.
$100.00 (FYC) * 170% (broker's percentage) = $170.00
(amount John Doe receives from the $1000.00 premium)

Company B: Sells $1000.00 in stock premiums.
$100.00 (FYC) * 170% = $170.00 OR an amount that has to
be manually entered depending on the insurance company

Our company receives a "cut" of the commission in this way:
Depending on the insurance company, the rate is a
percentage and can be 185%, 200%, etc. Thus,
our cut is:
FYC * (200% (depends on the ins. company) - broker's %)
$100.00 * (200% - 170%) = $100.00 * 30% = $30.00.

For each broker, these commissions must be tracked for EACH insurance company on a monthly basis as well as a Year-To-Date.

The data we must track for John Doe's activity would be:

Thus, to answer Willir's questions:

1) Can a broker sell for more than one company? Yes. He/She can sell for any of them.
2) Will a company always pay "X" % for insurance and "Y" for money? Not always. Each company offers both types of sales (traditional and "money&quotand pays commission differently for each type. For the traditional insurance sale, all but 3 uses (FYC * broker %) (the other 3 offer no commission) but gives a flat broker commission (which varies, hence the need to manually enter these amounts).
3) Problem tracking commissions for multi-years (i.e. 2004, 2005, etc). The system will be purged at year end and start fresh.

The data collected for John Doe each month is:

His name
His commission rate.
His monthly commission for each company he had sales.
His total monthly commission for all companies.
His total monthly FYCs (base commissions).
His total FYC for the YTD.
Our company's monthly commissions generated by that broker.
Our company's YTD commissions generated by that broker.

I hope this helps.

I'm in the process of re-evaluating my tables, etc and will attempt to normalize them. I will post when finished.

Again, I honestly appreciate all the help and suggestions you've given me so far.

Mark

 

[MarkRCC]

Okay. I've done a bit of thinking and modifying and here's what I have. Am I getting closer or just running in circles?

I now have three tables as follows:

tblBrokerInfo
brkrID - Primary Key (links to tblBrokerMonthlyData)
brkrLN - Broker's Last Name
brkrFN - Broker's First Name
brkrCompanyName - name of company if no ind broker
brkrOffLoc - Location of broker's office
brkrORRateA - Broker's commission rate (in %)
brkrORRateIA - Broker's commission rate (if sale was
with a single, specific company)

tblBrokerMonthlyData
BrokerID - links with <tblBrokerInfo.brkrID> field
CompanyID - links with <tblCompanyInfo.CompanyID> field
BrokerFYC - Broker's base commission rate (can change)
BrokerORMS - Broker's commission (Money Sales)
BrokerORLS - Broker's commission (Traditional Sales)
FHORLS - Our Company's commission on the broker's sale
(Traditional Sales)
FHORMS - Our company's commission on the broker's sale
(Money Sales)
Month - Month in which the sale was recorded
Year - Year in which the sale was recorded.

tblCompanyInfo

CompanyID - Primary Key - links to
tblBrokerMonthlySales.CompanyID>
CmpnyName - Name of the Insurance Company that the
broker represented for the sale.
TotalORRateLS - commission rate (in %) that our company
receives from this company for each
traditional sale.
TotalORRateMS - same as above for money sales.

Working on paper, all seems to link ok and all data seems to be present.

Any suggestions?

Many TIAs

Mark

 

[MarkRCC]

Willir? LesPaul? Golom? Anyone?

Still many, many TIAs!

Mark

 

[lespaul]

The only thing I can see at a quick glance that may need to be modified is:

tblCompanyInfo

CompanyID - Primary Key - links to
tblBrokerMonthlySales.CompanyID>
CmpnyName - Name of the Insurance Company that the
broker represented for the sale.
TotalORRateLS - commission rate (in %) that our company
receives from this company for each
traditional sale.
TotalORRateMS - same as above for money sales.

If the ORRateLS and the ORRateMS change over time, and you still want to be able to run reports of some kind (historical), you may need to change this to:

tblCompanyRates
RateID
RateType
CompanyID
StartingDate
EndingDate
RateAmt

or something along those lines. That way if the rate changes on 12/1/03, you will still be able to run historical information by checking the time period and the rate for that period.

Make sense?

Leslie

 

[MarkRCC]

It's making tonnes of sense! Many thanks for your expertise and your time.

I have a few questions about a form w/subform I'm trying to design, but I will post that in the &quot;forms&quot; forum. I hope you guys peruse that one as well...

Mark

 

[willir]

Mark

Sorry, I had to &quot;step out&quot; of town.

Your design seems fine.

Before moving to paper, I like to enter some dummy data and test my queries to ensure I get the results I am looking for. Much better to find out at this stage than later after a couple of months.

Leslie's suggestion on tracking rates is excellent. You are dealing with cash (now I know why my broker, who probably sells over $2mil, has two condos, an admin assitant, sems to work only hard two or three months of the year, and gets a new car each year as a write-off.)

Personally, I would go further. You suggest that the tables will be purged each year. Hmmm, I bet you a cup of coffee you will rename the table to include the year, and have the current one accumulate new data. (I suspect you will have a transition period where you are enter new data and some brokers are asking questions from the previous year.) With a bit of planning, you system could encompass &quot;years&quot; instead of the current year.

Nonetheless, it is your database, and you are off to a great start.

Richard

 

[MarkRCC]

Hi.

Thanks for all the GREAT advice. I have some questions about a particular form/subform, but will post them in the &quot;FORMS&quot; forum.

I appreciate all the advice and help that you fine folks so willingly give (especially to those of us (read: me!) who are new to Access).

Mark