Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
axisperfect
Starting Member
21 Posts |
 Posted - 2005-07-11 : 23:24:57
|
I've been given a user's requirement that just has me stumped on how to design my database. The gist of the whole system is this (where probable tables are in curly brackets): Participating merchants (e.g. my client's clients) {Merchants} sell {Transaction} their products {Products}. Each transaction (row) will have a total retail value (based on the retail value of product price and quantity sold). The money is paid to my client, which in turns will give the merchant his/her share based on the revenue scheme. Now, here's my problem, merchants can choose different revenue sharing schemes. The basic structures are below:1. flat rates 1a. percentage - 60% of total retail revenue is given to the merchant1b. amount - 2.50 is paid for every transaction made2. pyramidic - 2a. retail volume - for the first 500 retail value, a percentage of 50% of that total is given. For the next 500 retail value, a percentage of 55% of that total is given. For the balance (>1000), a percentage of 65% of that total is given.Now -- I can probably do some reporting module based on per merchant for a certain accounting period, something like, for given merchant, check revenue option column, calculate total revenue/total trasaction (from the {Transaction} table and then, according to scheme, find total revenue amount to be given for this period. It's an on the fly reporting, and I wince a little at how much calculations there'd be.. but I can't think of any other way..?  However, I'm completely stumped on how to give the clients an overview, e.g. how much revenue was given for all merchants to date.I'd have to calculate for all the merchants, for all the accounting periods that occurred to date.  Help? Any suggestions on how I could go about this? |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-12 : 05:29:07
|
Wouldn't you have a "Close period" routine that would calculate the Merchant's commission, for the period, and then perhaps create a "Payment" transaction? And then for reporting you could total the Payments for the date range of interest?Or is that too easy?  Kristen |
 |
|
|
axisperfect
Starting Member
21 Posts |
Posted - 2005-07-14 : 22:50:44
|
| Hehe, why could I think of this?! I guess I was trying to find some way to dynamically calculate an overview (as I would the accounting period) -- but this probably is a much better way. The tricky part, I suppose is what to do when an adjustment (e.g. refund) occur once a period has been closed. But then again, if payment is already made, you can't take it back from that period; you'd probably have to take it back from a current period. Hmmm. I think this is more of a process flow decision than a design one.Thanks! |
|
|
|
|
|
|
|
|
Revenue Sharing System - Design Stumped