Seeking a normalization expert & coach - who's in?!

[ML!]

I am seeking coaching from normalization experts as I build a data model for a temporary database that will house local data for now but will be translated to the website we're building. The web-based database will be developed by a professional programmer but we need a temporary solution since customers have already signed up and we're managing transactions and data manually. This exercise I hope will help me write finish writing the specs for the web app.

I'll try to explain the requirements by outlining a scenario for you...

• A brokerage offers an insurance type product to their clients (employers)
  
• A broker from the brokerage sells the product (we may also sell it directly so no commission on the transaction)
  
• The broker and the brokerage are paid a commission on the initial sale of the product and on each transaction
  
• The employer enrolls their employees in the program.
  
• The employer regularly deposits money into an account for the employee to spend on specific services
• The employee submits receipts for which they are reimbursed from the amount in their account

So thinking one step at a time, prior to figuring out accounts, transactions, etc, I thought I'd start out with the we relationships between the entities. We need to track brokerage info, brokers that work for the brokerage, commission rates, the employers that are clients of the broker, the employees that work for the employer and the accounts for the employees.

So my first question is...

Q1

The employer may have multiple contacts with authority relative to the transactions. Should I just flag those people with a type code in the employee table or put them in a separate table? My thought is add a field called type code (O = owner, A = admin, P = Professional Advisor, E = employee) to the employee table and then another table for the Code descriptions.

In this scenario, O's, A's and P's would have a permission level defined in the webapp down the road. For now, we'll just describe it because obviously they won't be logging into the local database. We still want to define them because we want to track who has the authority to, for example, change the amount (not E's) which they will do via phone or email for now.

Deposits would be made to the accounts of O's and E's but not to P's because they would be professional external advisors, such as a Chartered Accountant.

Employees
EmployeeID (PK)
TypeCode (FK)
EmployerID (FK)
FirstName
LastName
etc...

EmployeeCode
TypeCodeID (PK)
PermissionLevel (FK)
TypeDescription

Permissions
PermissionLevel (PK)
Description

Coaches - over to you! TIA

 

[ML!]

Q2

What would you suggest is the best practice for commissions? Commissions will be paid on to both brokers and their firms. They will also be paid upon the sign up fee and each deposit into the employee account. Rates may vary.

Does it make sense to add the actual rate to 2 fields in the Broker and Brokerage records or establish a commission table (or 2)?

 

[ML!]

Q3

I know I said I will concentrate on entities first but I'm reviewing this data model from @boblarsons sticky post for ideas

http://www.databaseanswers.org/data_models/online_banking/online_banking_showing_attributes.htm

In the Accounts table, it shows a field called current_balance. Correct me if I'm wrong but shouldn't the account balance be available only via calculations from transactions in reports and queries?

 

[spikepl]

A few thoughts that popped up:
1. Is the commission for a given broker fixed forever? If not, then you'd probably need a separate table to record the value and dates of change
2. Can a P be shared among many companies?
3. Remember audit trail requirements, if any
4. Subsequent changes in underlying data after a transaction, should persumably not change the record of the transaction itself.