Database Design

[alexander1113]

Dear DB Administrator Experts,

I am trying to design a database for a sales company who represent multiple suppliers and I am in stuck in how to go about the following scenario. An order is placed, and there is an amount paid on that order. From that amount paid on the order, our sales company gets a commission piece off it and from that commission we get, it gets split between either one sales rep or multiple sales reps. So for example, let's say someone places an order off Supplier A for 1000 dollars. Supplier A gets that order and we know that they have to pay us 10% for that order. So we get a check for 100 dollars. Now from that 100 dollars, it's split in between the sales rep or reps and the sales company. so it could go for something like, .8 for in house, .1 for salesrep A, .1 for sales rep B. So now I'm trying to interpret this in the tables, and i got the two tables:

INVOICE:

ordernum -- ordernumber
invoice_num --invoice number for check amt sent
status
amt_paid -- amount of check

INVOICE DETAILS:

ordernum
invoice
commission_percentage --percentage rep gets ex. .1, .2
rep_id -- sales rep number
date_of_pay -- date paid
comments
amt_paid --how much paid based on the commission perc.

Now with invoice details table there will be duplicates because like i stated before, there will be multiple reps perhaps paid on one invoice, aside from rep_id # 00 which is in house. Now is there anything I'm doing wrong or is this design efficient. If you need more information , please ask. Thank you ahead of time.

Alex

 

[Ammodog]

I would have 3 tables one for your sales information, one for the sales rep, and one for the sales company information. Let access assign an ID to each record in each table. Then in the queries you can bring that information together and do all of your financial calculations.





Christopher Abney
There is no limit to the good you can do if you dont care who gets the credit

 

[alexander1113]

Hi Ammodog,
Can you give me an example?

Alex

 

[DanJR]

Hi alexander1113,

i may be of little help becuase i haven't modelled invoices/orders before, for a business. However, could you provide more information about the commission. You state the commission gets divided between one or more sales reps but in your example you show that it also gets divided between other "things" e.g. "in house". Is "in-house" just another term for over-heads? What else could it be divided into?

Also, i'm unsure what the order details table is...does the date of pay and amount paid refer to paying the sales rep or refers to what the customer paid?

Now with invoice details table there will be duplicates because...

There doesn't need to be any unnecessary duplicates - this usually implies further normalisation is appropriate.

...there will be multiple reps perhaps paid on one invoice...

This suggests a sales rep table which links a sales reps commissions to the invoice, 1:M relationship.

One example could be:

INVOICE:
  invoice_number (PK)

COMMISSION:
  invoice_number (PK)(FK)
  rep_id (PK)
  commission_pct

OVERHEADS:
  invoice_number (PK)(FK)
  overhead_pct

If the invoices are final (can never be changed) then you can store 'amounts' instead of percentages. Other examples exist....

Cheers,
Dan

 

[ceh4702]

One question you might ask is will the amount sold always equal the amount shipped? What happens when you have an out of stock or a back-order issue? Do you only ship what you have? Do you send out partial shipments? Do you wait untill the order is complete or ship what you have and let the customer know that your dropped part of his order off of the shipment or what?

If you are basing the sales based on the amount shipped you may want an inventory control table, so you can reduce the inventory level every time you take an order so you have real-time stock levels when you take orders.

If you base your commission on what goes out the door you have to keep track of the invoices and the invoices have to be linked to the orders. This may take a table to join them with the order number and the invoice number and the salesmen/employee ID. If you have to link employees to multiple companies the company prefix could be added to the employee number for intelligent coding or not.

A lot depends how many tables you want to have to go through when you do reports or querries. You have to design the data for extraction, not entry.

If you do not like my post feel free to point out your opinion or my errors.