defining the project

For these tutorials we are going to create a database application for a fictional company called "Profsr Financial Services".

Actually, although the name is invented, the application is real. The samples you will see refer to actual data and only the names have been changed to protect the privacy of everyone involved.

Here's an aside for budding developers: it's amazing how much your clients will trust you! Especially if you work with small or medium-sized businesses. You will be given access to all kinds of privileged information. You usually need real data to test the apps that you're developing but you have to make sure that it never goes further than that. After the testing is complete destroy all test data or modify it enough that it can never be traced back to any person or organization. Customer lists, product designs, financial statements, etc. can be valuable to competitors and all kinds of other people. Remember that as a professional you have an obligation to your client to protect their interests.

If you have to use client data for demonstrations or proposals, like I'm doing here with an actual project, use samples that have been thoroughly vetted for public use.

what the client wants

Profsr Financial Services is a privately-owned broker for insurance, mutual funds and other financial instruments.

They need a database to track sales and commissions for 30 or so sales professionals. As is usually done in the business, sales persons are paid only on commission based on their performance. Their sales have to be analyzed constantly over time (weekly, monthly, annually) and by product and product line because certain persons specialize in certain product lines or specific products that may or may not be more profitable than others.

The commission rate is different for different products. It may be calculated on the premium paid in the case of insurance or on the invested amount in the case of funds. There is also a second commission level which represents the amount paid to the agency.

Typically a client will buy only one product. There is no need to keep any customer data. The customer's contact info and so on is kept on paper in a file.

How it works is this: a sales rep meets the customer and then writes a proposal. The proposal is submitted to the office. The date on which it is submitted becomes the date of the sale and the proposal becomes in fact the contract. A small number of proposals are rejected (life insurance, for example) and those will stay in the system but they will be flagged so that they will not be include in the sales reports.

Most proposals are written by one sales rep. However, in a certain number of cases involving specialized products, there may be more than one sales rep involved. The commission will be based on each rep's degree of participation, agreed to between themselves: for example, 60% for Jane and 40% for Tom. In fact, for institutional sales, there may be 5 or 6 sales reps involved. In that case each rep in the team will get a percentage of the commission, determined at the time of the sale.

Sales and commissions reports will be produced as required, usually based on dates. Different groupings will be needed to analyze products, product lines, special promotions and so on.

preliminary design

If you've studied the previous tutorials, you should recognize this as a fairly normal master/detail kind of structure.

But there is one major difference.

In the normal sales operation where you produce an invoice, you've got one customer buying several products from one salesman.

Here you have one customer buying one product from several salesmen.

So, the master and detail tables will be somewhat different. The relationship between the entities is: one product --> many sales reps. The product info in the Master table and the Sales rep info in the Detail table.

After a bit of work (OK, a lot of work!) we come up with a design for our database:

Study the diagram. There are some aspects that may seem mysterious but they will be covered as we develop the model.

In the next tutorial we'll start building the database.