How would YOU structure this?

[stevelucky]

I'm pretty new to FileMaker Pro but have quite a technical background and pick things up pretty quickly. Let me lay out the scenario of what I'm trying to accomplish and I'd like to hear how you would structure it.

I have several sales reps. Each of these sales reps has multiple accounts that they've signed. There are commissions associated with these accounts that they get paid.

I'm trying to create a database where I can enter in personnel info for individual reps to be displayed at the top of the page. Beneath that there would be 2 or 3 tabs associated with their accounts. On one tab I would like to be able to add their accounts, one row for each account. I want to be able to add many accounts here as this will grow over time. On each of these rows I will have fields corresponding to their commissions that get paid out over a one month period. I will then have a 2nd tab that keeps track of commissions they get every month from these accounts.

My question is, does this need to be done using two tables? 1 for the rep info and 1 for their accounts? Or should I just use one table?

Please let me know if you need any more info.

Edited March 24, 2010 by Guest

[David McQueen]

Three minimum

Reps

Accounts related to reps

Sales and resulting commissions related to accounts

Potentially also a companies table for large comanies with more than one account

Then if a salesman leaves, the account is just reassigned and commissions due stay related to the salesman and new commissions go to the correct party.

It also allows you to build your sales and commissions reports later.

Just my thoughts

[stevelucky]

This sounds right to me. So, how do I tie those tables together? I know that's a big question, but if you could just get me started in the right direction, I would appreciate it.

[David McQueen]

All keys must be indexed.

Reps has a prime key - a unique identifier in each record. I use an auto-entered serial number of form text - but that's just me.

Accounts has a Prime Key and a Reps key ( I am assuming one Rep per account or you need a join table)

Sales has Prime Key, Accounts Key and Reps Key

Every account has a Rep. Every Sale has both a Rep and an Account attached to it.

From the Rep, you should then be able to see all of his/her accounts and all of his /her sales.

If you go to sales through accounts, you can use accounts to filter sales by account.

You are started -)

[stevelucky]

I know I'm going to have more questions but we'll just start with this: Do I need a prime key for the reps? Can't I just tie them to the accounts by their name?

Also, all accounts will have a unique identification number assigned to them through another system. If I have a field for this, will this be able to take the place of the prime key for the accounts?

If all that is accurate, I just need to figure out how to get it all to display on the same page. Ideally I would like the left hand side of the page to contain my rep info (contact info, hire date, etc...) and the left hand side have a tabbed window with tabs for accounts (with both a list of their accounts and a small form for creating new accounts for the rep listed on the left side of the screen) and a tab for commissions paid out (I'll deal with that part later).

[David McQueen]

I know I'm going to have more questions but we'll just start with this: Do I need a prime key for the reps? Can't I just tie them to the accounts by their name?

1. Having a program generated Prime Key in each table is generally considered good practice. this key is not for the user but for you as programmer.

2. You may get away with using their names. I have done it. but not the best practice.

Also, all accounts will have a unique identification number assigned to them through another system. If I have a field for this, will this be able to take the place of the prime key for the accounts?

Put in a prime key. It sounds like you have data from a third party. If so, there is no guarantee that they will not change their system.

If all that is accurate, I just need to figure out how to get it all to display on the same page. Ideally I would like the left hand side of the page to contain my rep info (contact info, hire date, etc...) and the left hand side have a tabbed window with tabs for accounts (with both a list of their accounts and a small form for creating new accounts for the rep listed on the left side of the screen) and a tab for commissions paid out (I'll deal with that part later).

Think portals instead of tabs.

Selecting a rep gives a portal of accounts and the Rep information. Clicking on an account sets an account key in a global field that gives you a portal of that rep's sales.

Clicking on the sale entry in the portal sets another key that gives access to the sale information if required.

Tabs work when everything is sitting in the same table.

So....

You have the Rep selected in the rep table and see accounts - one relationship

You click on the account and set a global field in reps that matches g_account key to account_prime to see the sales for that account - two relationships

You click on the sale and that sets g_sale Key that matches Prime Key Sales to see the details of the sale - three relationships

All coming out from Reps

HTH