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

Something like what you have could work, as long as Invoice Details has a M:1 relationship with Invoice. Also, you might want an entry for "company" in invoice details, to keep track of how much the company makes. I'm not sure you need both order number and invoice number in the invoice details table.

Hi ByteRyder52,
Thank very much for your comments. Yes there is a M:1 relationship. I kind of need ordernum and invoice num because, i need to keep track of which invoice pertains to an order number. So order number 234 may have an invoice of 23. And Order Number 235 may have the same invoice of 23 although they are totally different. Order number never duplicates, but invoice may. Thanks again. Anymore comments are welcome.

Alex