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 |
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
 Posted - 2004-08-05 : 01:06:48
|
Ok here's a complicated (or maybe not) situation. Any help is greatly appreciated.Intro:We have a clients table and a salesrep (as SR) table. We also have an invoices table with a clientID FK. We are trying to detrmine the best way to link the salesrep table to the other two tables. but take these things into consideration:I thought about having one salesrep per client, but the "WHAT-IFs" haunt me! "WHAT IF ONE DAY a huge client must have 2 or more salesreps, maybe a presentation must be prepared, and each SR should get a portion of the commission." This means i would have to have a many to many table listing the clients and the SRs (both FKs, one client can have many SRs, and one SR can have many clients...I know you know what the many to many means, just like to clarify.) Well this brings up interesting ideas each with it's own unique problems.1) We sell a service, (pre)paid on a monthly basis. If client ABC creates an invoice, INV1 with SR123, on jan 1st for 1 year, SR123 get's the commision, and then on june 1st the client's SR was replaced by SR456 and created a new invoice, INV2, for another year, SR456 get's the commission. Pretty typical. Since I sell a service, if a client decided to cancel, I cannot pay commission to my SR for the unused portion; for exmple, if client ABC cancels INV1 on month 8, I must 'decommission', for a lack of a better word, 4 months of commission from SR123 (since when the invoice gets created, I add a credit, in a table salesrepcommissions, the sales commission for that invoice. I would then add another row to debit the unused portion when a service is cancelled. Confuzzled yet?) not the current SR, obviously. Anyhow, what if this happened to be a special oocassion where SR123 was not to be replaced by SR456, but instead SR123 allowed SR456 to take full commission for INV2 while SR123 was still an active SR for client ABC. At the application level I would have to have a way to tell the computer to add commission NOT based on the current SRs, but instead on, maybe, a list of chosen SRs, which would be a list of the active SRs, maybe? One problem to this would be if SR123 trusts SR456 to add him/her to the invoice so they both get the commission, but SR456 forgets, either accidentally or purposely, to add SR123, then problems will arise. All I could think of would be to have company policies make SRs responsible for these situations and make sure they are added to the invoice at the time it was made, by whatever means, like both SRs be present at the time the inoice is entered into the system to make sure the other adds him/her correctly.I have a friend who works at a fairly large company that has a billing program that only takes into consideration the 'current' salesrep! Wow! You should see, or maybe someof you can imagine the problems that occur when a client cancels/returns a shipmentthat was made a few months back and there happens to be a new salesrep. Before canceling the invoice, the 'biller' MUST be able to know WHO was the salesrep for that order so he/she must change the current salesrep to the old one, then cancel the invoice, then change the current salesrep back to the actual current salesrep. Terrible design I think, and it's a fairly big program.2) that takes care of adding the the SRs to the invoices, but should this be on another many to many table for the invoice-salesrep tables? Which would be, as described above, the chosen SRs from the list. Obviously only show in the list the active SRs for that client.Wow! Well I hope no one breaks their heads over this. And if you already have, please help me repair mine.  Any and all comments are welcome, so please feel free to comment/give some advise on this.Thanks in advance to everyone who reads/joins.- RoLY roLLs |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-05 : 01:19:11
|
| ClientSalesRepSalesRepTeamSalesRepIDTeamIDEffDateEndDateCommissionStatus1CommissionStatus2 --Could describe commission status, rollover status on termination, etc, etc. Could by type table key.InvoiceInvoiceIDSalesRepTeamID--OR--InvoiceCommissionTotalInvoiceSaleMatrixInvoiceIDSalesRepTeamIDCommissionPctCommissionTypelalalala ...... so many ways to stub a toe.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-08-05 : 02:09:19
|
| thanks derrick, stub that toe a few more times, will ya! I like your solution #1, since each salesrep will be commissioned differently based on their current rate even tho they will be on the same team. Or should it not be this way? I ask this because I can make the business rules right now depending on the outcome of this post, and other discussions I have with my partners. What are typical business rules you, or others, have encountered, and what are the pros/cons? This way we can pick out a rule that best fits our needs, either by suggestions on here or others we may think of.- RoLY roLLs |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-05 : 08:32:27
|
| Well, it depends on how complex they want to get with this. Notice that solution #2 uses the exact same structure of #1. It just modifies the Invoice table and adds the InvoiceSaleMatrix table. What this is giving you the ability to do is modify on a per invoice basis the commission information. It's a little overboard. I like the direction you're trying to head with this, so I would run it by your business if the team concept would solve your issue. This gives you the ability to "overlap" sales team members, or have a running relationship between team members that have been removed from a sale and replaced. It also gives you the ability to apply specific rules, commission percents, bonus structures, etc at the team level instead of having to replicate it for each level. The question is do you need to "matrix" or create further hierarchies of the teams and/or invoice, sale, and client/company relationships.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-08-05 : 10:51:46
|
| We'll have to run by some scenarios and determine what will be the outcome in order to answer the 'matrix' question. One thing that we're looking at to determine the best solution as that not always will the same salesreps be on the same team and, most likely, salesreps may do the jobs alone for smaller clients, which will be the majority focus at this point. So this means that many teams would be created each time a member moves from one team to another. It can be on a day by day basis, so actually, solution #1 would at some point be huge with all the possible combinations. With just 5 salesreps you have 28 different combinations of teams, in many cases, all 28 combinations may never happen. So only the combinations would be entered when they occur. Or am I not seeing your solution correctly?Also, since not every, but only a few or several clients may not have a salesrep, i was thinking of a design more like this:clientclientidsalesrepsalesrepidcommissionrateinvoiceinvoiceidclientid <--forgot thisclientsalesreps (these are the current reps)clientidsalesrepideffdateenddate(commission rate) - maybe have the commission rate here, per client instead of per salesrep(commission to use) - and maybe add this to say which comm to use, either the client or the salesrep's?invoicesalesreps (these would be the reps involved at the time the order took place)salesrepidinvoiceid(commission rate) - maybe have the commission rate here, per invoice instead of per salesrep or client(commission to use) - and maybe add this to say which comm to use, either the client or the salesrep's or the invoices?We have so many ideas but we want more, so we can see the big picture first, then narrow our solutions to fit out needs and to what works best with possible scenarios.- RoLY roLLs |
|
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-08-05 : 10:52:42
|
| BTW: Derrick, not sure what the commissionstatus 1 & 2 would be for, would you mind elaborating a little more? thanks!- RoLY roLLs |
|
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-08-06 : 13:17:43
|
| Anyone have any comments on this pros/cons?- RoLY roLLs |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-08-06 : 14:13:05
|
| Comment:Prototype it in Access...Do you have ERWin?Brett8-) |
|
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-08-06 : 14:14:10
|
| ERWin? what's that?- RoLY roLLs |
|
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-08-06 : 15:42:49
|
| ok i got the evaluation of ERWin, let's see what happens when I try to open it - RoLY roLLs |
|
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-08-06 : 16:24:11
|
| Ok, I opened it played with it a bit, seems like a great program, but unfortunately keeps me on the same boat for now, deciding on how to model the database.Thanks Brett.- RoLY roLLs |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-06 : 16:54:41
|
quote:
Originally posted by RoLYroLLs
BTW: Derrick, not sure what the commissionstatus 1 & 2 would be for, would you mind elaborating a little more? thanks!- RoLY roLLs
It was just optional fields to display commission percents, etc. etc.Also, you are right on the teams. You would only have combinations when they actually occur. This is just an idea to get you started thinking. It really is hard to model "long distance". You need to know what the business needs, and how the data processes work to properly model a good long-term solution.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-08-06 : 17:28:58
|
quote:
Originally posted by derrickleggett
It really is hard to model "long distance". You need to know what the business needs, and how the data processes work to properly model a good long-term solution.
Yeah, we are in the process of created the 'business needs' by gathering information on possible situations sales reps can deal with clients in order to create a good long-term model. I guess this question is less related to this forum. Thought I'd give a try to see what other developers have done in the past with their business' needs on this sorta topic and work from there on choosing one for ours.  - RoLY roLLs |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-06 : 17:37:31
|
| You might want to run a "prototype" design by us after you get done creating the business needs. See what feedback we have at that point.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-08-07 : 15:48:55
|
How's this look? Any ideas/comments accepted. Where ClientSalesRep is the list of the current salesreps for the clients and the invoice salesreps is the list of salesreps that were involved for a particular invoice- RoLY roLLs |
|
|
|
|
|
|
|
|
Database Design: SalesRep