If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Advice on table setup
Hello all,
I am banging my head against the wall. We are adding some functionality to our database but I have two solutions for the problem and cannot decide on one or the other. I have waffled back and for and tried to create prototypes of each and experiment with them but I still cannot come to a consensus. Other than someone’s experience I believe the only way I will be able to decide is to attempt both ideas, and I don’t think management will approve that! What we have currently is tblCustomers and tblOrders. Each customer can have many or none orders. Now we want to add a table for Sales Reps. Each Customer can have one or many Sales Reps. Each Order must have one Sales Rep. My first impulse is to slide a table between tblCustomers and tblOrders, tblSalesReps. I know this is the right way to do it, but since this will involve almost every part of the database being changed I am wary of jumping into it. I am investigating the users needs more to better understand the problem, but what are the advantages to this approach and what are the disadvantages to this approach. But as I looked for alternatives, I came up with this idea. I will have tblCustomers having a one to many relathionship each with tblSalesReps and tblOrders. Then tblSalesReps will have a one to many relationship with tblOrders. It creates a circle of tables. It just seems wrong. Has anybody ever tried an approach similar to this? Is this a standard approach or is a crackpot idea to save me work. What are the advantages to this approach and what are the disadvantages to this approach? Am I going to paint myself into a corner? If my descriptions are not clear, I have screen shots of the relationships I can email you. Any advice or comments will help me along! Thank you -Rick |
#2
|
|||
|
|||
Advice on table setup
On Fri, 23 Jul 2004 08:48:02 -0700, "Rick Vooys"
wrote: What we have currently is tblCustomers and tblOrders. Each customer can have many or none orders. Now we want to add a table for Sales Reps. Each Customer can have one or many Sales Reps. Each Order must have one Sales Rep. My first impulse is to slide a table between tblCustomers and tblOrders, tblSalesReps. I know this is the right way to do it, but since this will involve almost every part of the database being changed I am wary of jumping into it. I am investigating the users needs more to better understand the problem, but what are the advantages to this approach and what are the disadvantages to this approach. I'd suggest that since "each Customer can have one or many Sales Reps" and "each Order must have one Sales Rep" - and, I'm sure, "each Sales Rep will have one or many Orders" - that you link Orders to Sales Reps; if you need to link a sales rep to a customer, you can do so via that customer's Orders. The Orders table here is functioning (in part) as the "resolver table" for the many to many link between Customers and Reps. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#3
|
|||
|
|||
Advice on table setup
"=?Utf-8?B?UmljayBWb295cw==?="
wrote in : Now we want to add a table for Sales Reps. Each Customer can have one or many Sales Reps. Each Order must have one Sales Rep. This to me speaks of (perhaps) two separate relationships. Do Customers relate directly to their SalesReps independently of their Orders, or does a SalesRep only acquire the SalesRep on account of buying something from him/ her? You definitely have this relationship: Orders Customers ====== ========= InvoiceNumber SalesReps CustID --------- CustomerID ================ AssignedTo ------- SalesRepInitials .... which just says that every order must pertain to exactly one customer and exactly one rep. You might have this relationship too: Customers TalksTo ========= ======= SalesReps CustID --------- CustomerID ================ Rep ------- SalesRepInitials which is a _separate_ relationship that holds details like "Ace Products" has contacts with Dan and Eric and Fred, *even* though they may *never* have completed an order with them. Database design is all about semantics. How many types of relationship can there be between reps and customers: DependsOn; InformationOnNewProducts; WheelsAndTyresOnly; etc etc? Hope that helps Tim F |
#4
|
|||
|
|||
Advice on table setup
I would recommend John's solution, the current salesrep=20
for a customer can then be found by checking his/her most=20 recent order. You could generate a blank order for a new=20 customer just to assign the rep. The advantage here would be storing the salesrep exactly=20 once per order and you won't have to change it in two=20 places if another rep takes over (or assists) that=20 customer. -----Original Message----- Hello all, I am banging my head against the wall. We are adding=20 some functionality to our database but I have two=20 solutions for the problem and cannot decide on one or the=20 other. I have waffled back and for and tried to create=20 prototypes of each and experiment with them but I still=20 cannot come to a consensus. Other than someone=E2?Ts=20 experience I believe the only way I will be able to decide=20 is to attempt both ideas, and I don=E2?Tt think management=20 will approve that! What we have currently is tblCustomers and tblOrders.=20 Each customer can have many or none orders. Now we want to=20 add a table for Sales Reps. Each Customer can have one or=20 many Sales Reps. Each Order must have one Sales Rep.=20 My first impulse is to slide a table between tblCustomers=20 and tblOrders, tblSalesReps. I know this is the right way=20 to do it, but since this will involve almost every part of=20 the database being changed I am wary of jumping into it. I=20 am investigating the users needs more to better understand=20 the problem, but what are the advantages to this approach=20 and what are the disadvantages to this approach. But as I looked for alternatives, I came up with this=20 idea. I will have tblCustomers having a one to many=20 relathionship each with tblSalesReps and tblOrders. Then=20 tblSalesReps will have a one to many relationship with=20 tblOrders. It creates a circle of tables. It just seems=20 wrong. Has anybody ever tried an approach similar to this?=20 Is this a standard approach or is a crackpot idea to save=20 me work. What are the advantages to this approach and what=20 are the disadvantages to this approach? Am I going to=20 paint myself into a corner? If my descriptions are not clear, I have screen shots of=20 the relationships I can email you. Any advice or comments=20 will help me along! Thank you -Rick . |
#5
|
|||
|
|||
Advice on table setup
I wish I could post a diagram so I can be sure I am hearing you right. I'll try to
do it with characters and draw out what I am getting from your responses. I'd suggest that since "each Customer can have one or many Sales Reps" and "each Order must have one Sales Rep" - and, I'm sure, "each Sales Rep will have one or many Orders" - that you link Orders to Sales Reps; if you need to link a sales rep to a customer, you can do so via that customer's Orders. The Orders table here is functioning (in part) as the "resolver table" for the many to many link between Customers and Reps. So if I do it as John has suggested it would look like this: tblCustomers tblOrders tblSalesReps -------------- --------------- ------------ CustomerID ------------ OrderID ---------- SalesRepID CustomerID SalesRepID But I think the terminology I used may have been incorrect. Each Customer is actually a dealer. And each dealer has SalesReps. Now each order is currently assigned a Dealer and we track how well each dealer is doing for us. How much sales, what kind of sales. The new development will to capture and store the Salesrep so we can now track how they are doing. So when the user enters an Order they would select a Dealer(Customer) and next select a Salesrep. That list must be limited to Salesreps for that dealer. Does that change what you are suggesting. As I read Tim's response I realized there may be some confusion. This is my first solution from the orginal message. tblCustomers tblSalesReps tblOrders -------------- --------------- ------------ CustomerID ------------ SalesRepID ---------- OrderID CustomerID SalesRepID And this is the second solution. Which gives me referentail integrity throughout so that if a Sales rep's data is changed, I.E name change or something it is casscaded down. tblSalesReps tblCustomers tblOrders -------------- --------------- ------------ SalesRepID ------------ CustomerID ---------- OrderID CustomerID ---------------------------------------- CustomerID SalesRepID I guess the first question is; Does any of this new information change your suggestions? If so what would you advise. If not maybe you can explain a bit more why your response would work in this situation. The second question is; Are there any drawbacks to doing the second way. I know what I learned in school leads me to think the first way, but my gut says the second way is not completely wrong? Thanks for all your help, this is great! -Rick "John Vinson" wrote: On Fri, 23 Jul 2004 08:48:02 -0700, "Rick Vooys" wrote: What we have currently is tblCustomers and tblOrders. Each customer can have many or none orders. Now we want to add a table for Sales Reps. Each Customer can have one or many Sales Reps. Each Order must have one Sales Rep. My first impulse is to slide a table between tblCustomers and tblOrders, tblSalesReps. I know this is the right way to do it, but since this will involve almost every part of the database being changed I am wary of jumping into it. I am investigating the users needs more to better understand the problem, but what are the advantages to this approach and what are the disadvantages to this approach. I'd suggest that since "each Customer can have one or many Sales Reps" and "each Order must have one Sales Rep" - and, I'm sure, "each Sales Rep will have one or many Orders" - that you link Orders to Sales Reps; if you need to link a sales rep to a customer, you can do so via that customer's Orders. The Orders table here is functioning (in part) as the "resolver table" for the many to many link between Customers and Reps. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#6
|
|||
|
|||
Advice on table setup
I wish I could post a diagram so I can be sure I am hearing you right. I'll try to
do it with characters and draw out what I am getting from your responses. I'd suggest that since "each Customer can have one or many Sales Reps" and "each Order must have one Sales Rep" - and, I'm sure, "each Sales Rep will have one or many Orders" - that you link Orders to Sales Reps; if you need to link a sales rep to a customer, you can do so via that customer's Orders. The Orders table here is functioning (in part) as the "resolver table" for the many to many link between Customers and Reps. So if I do it as John has suggested it would look like this: tblCustomers tblOrders tblSalesReps -------------- --------------- ------------ CustomerID ------------ OrderID ---------- SalesRepID CustomerID SalesRepID But I think the terminology I used may have been incorrect. Each Customer is actually a dealer. And each dealer has SalesReps. Now each order is currently assigned a Dealer and we track how well each dealer is doing for us. How much sales, what kind of sales. The new development will to capture and store the Salesrep so we can now track how they are doing. So when the user enters an Order they would select a Dealer(Customer) and next select a Salesrep. That list must be limited to Salesreps for that dealer. Does that change what you are suggesting. As I read Tim's response I realized there may be some confusion. This is my first solution from the orginal message. tblCustomers tblSalesReps tblOrders -------------- --------------- ------------ CustomerID ------------ SalesRepID ---------- OrderID CustomerID SalesRepID And this is the second solution. Which gives me referentail integrity throughout so that if a Sales rep's data is changed, I.E name change or something it is casscaded down. tblSalesReps tblCustomers tblOrders -------------- --------------- ------------ SalesRepID ------------ CustomerID ---------- OrderID CustomerID ---------------------------------------- CustomerID SalesRepID I guess the first question is; Does any of this new information change your suggestions? If so what would you advise. If not maybe you can explain a bit more why your response would work in this situation. The second question is; Are there any drawbacks to doing the second way. I know what I learned in school leads me to think the first way, but my gut says the second way is not completely wrong? Thanks for all your help, this is great! -Rick "John Vinson" wrote: On Fri, 23 Jul 2004 08:48:02 -0700, "Rick Vooys" wrote: What we have currently is tblCustomers and tblOrders. Each customer can have many or none orders. Now we want to add a table for Sales Reps. Each Customer can have one or many Sales Reps. Each Order must have one Sales Rep. My first impulse is to slide a table between tblCustomers and tblOrders, tblSalesReps. I know this is the right way to do it, but since this will involve almost every part of the database being changed I am wary of jumping into it. I am investigating the users needs more to better understand the problem, but what are the advantages to this approach and what are the disadvantages to this approach. I'd suggest that since "each Customer can have one or many Sales Reps" and "each Order must have one Sales Rep" - and, I'm sure, "each Sales Rep will have one or many Orders" - that you link Orders to Sales Reps; if you need to link a sales rep to a customer, you can do so via that customer's Orders. The Orders table here is functioning (in part) as the "resolver table" for the many to many link between Customers and Reps. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#7
|
|||
|
|||
Advice on table setup
I wish I could post a diagram so I can be sure I am hearing you right. I'll try to
do it with characters and draw out what I am getting from your responses. I'd suggest that since "each Customer can have one or many Sales Reps" and "each Order must have one Sales Rep" - and, I'm sure, "each Sales Rep will have one or many Orders" - that you link Orders to Sales Reps; if you need to link a sales rep to a customer, you can do so via that customer's Orders. The Orders table here is functioning (in part) as the "resolver table" for the many to many link between Customers and Reps. So if I do it as John has suggested it would look like this: tblCustomers tblOrders tblSalesReps -------------- --------------- ------------ CustomerID ------------ OrderID ---------- SalesRepID CustomerID SalesRepID But I think the terminology I used may have been incorrect. Each Customer is actually a dealer. And each dealer has SalesReps. Now each order is currently assigned a Dealer and we track how well each dealer is doing for us. How much sales, what kind of sales. The new development will to capture and store the Salesrep so we can now track how they are doing. So when the user enters an Order they would select a Dealer(Customer) and next select a Salesrep. That list must be limited to Salesreps for that dealer. Does that change what you are suggesting. As I read Tim's response I realized there may be some confusion. This is my first solution from the orginal message. tblCustomers tblSalesReps tblOrders -------------- --------------- ------------ CustomerID ------------ SalesRepID ---------- OrderID CustomerID SalesRepID And this is the second solution. Which gives me referentail integrity throughout so that if a Sales rep's data is changed, I.E name change or something it is casscaded down. tblSalesReps tblCustomers tblOrders -------------- --------------- ------------ SalesRepID ------------ CustomerID ---------- OrderID CustomerID ---------------------------------------- CustomerID SalesRepID I guess the first question is; Does any of this new information change your suggestions? If so what would you advise. If not maybe you can explain a bit more why your response would work in this situation. The second question is; Are there any drawbacks to doing the second way. I know what I learned in school leads me to think the first way, but my gut says the second way is not completely wrong? Thanks for all your help, this is great! -Rick "John Vinson" wrote: On Fri, 23 Jul 2004 08:48:02 -0700, "Rick Vooys" wrote: What we have currently is tblCustomers and tblOrders. Each customer can have many or none orders. Now we want to add a table for Sales Reps. Each Customer can have one or many Sales Reps. Each Order must have one Sales Rep. My first impulse is to slide a table between tblCustomers and tblOrders, tblSalesReps. I know this is the right way to do it, but since this will involve almost every part of the database being changed I am wary of jumping into it. I am investigating the users needs more to better understand the problem, but what are the advantages to this approach and what are the disadvantages to this approach. I'd suggest that since "each Customer can have one or many Sales Reps" and "each Order must have one Sales Rep" - and, I'm sure, "each Sales Rep will have one or many Orders" - that you link Orders to Sales Reps; if you need to link a sales rep to a customer, you can do so via that customer's Orders. The Orders table here is functioning (in part) as the "resolver table" for the many to many link between Customers and Reps. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#8
|
|||
|
|||
Advice on table setup
In my opinion, your second solution makes a whole lot more sense. I don't
see the sense in looking in the orders table to determine what sales rep work with each customer/dealer. Adding a dummy sales order to accomplish this is even more off center (no disrespect, whatsoever). You need an index table relating each dealer with their sales reps: Cust/RepIdx: ------------------- CustomerID SalesRepID tblSalesReps Cust/RepIdx tblCustomers tblOrders -------------- ----------------- ------------- -- ------------ SalesRepID ------- SalesRepID CustomerID --------- CustomerID CustomerID ---------- CustomerID SalesRepID ------------------------------------------------------------------- SalesRepID If an Order can have more than one SalesRep, you'll need to insert an additional table: Order/RepIdx: ------------------- OrderID SalesRepID tblSalesReps Cust/RepIdx Order/RepIdx tblCustomers tblOrders -------------- ----------------- ------------------ --------------- ------------ SalesRepID ------ SalesRepID CustomerID ------------------------------- CustomerID CustomerID ---------- CustomerID SalesRepID -------------------------------- SalesRepID OrderID -------------------------------------------- OrderID "Rick Vooys" wrote in message ... I wish I could post a diagram so I can be sure I am hearing you right. I'll try to do it with characters and draw out what I am getting from your responses. I'd suggest that since "each Customer can have one or many Sales Reps" and "each Order must have one Sales Rep" - and, I'm sure, "each Sales Rep will have one or many Orders" - that you link Orders to Sales Reps; if you need to link a sales rep to a customer, you can do so via that customer's Orders. The Orders table here is functioning (in part) as the "resolver table" for the many to many link between Customers and Reps. So if I do it as John has suggested it would look like this: tblCustomers tblOrders tblSalesReps -------------- --------------- ----------- - CustomerID ------------ OrderID ---------- SalesRepID CustomerID SalesRepID But I think the terminology I used may have been incorrect. Each Customer is actually a dealer. And each dealer has SalesReps. Now each order is currently assigned a Dealer and we track how well each dealer is doing for us. How much sales, what kind of sales. The new development will to capture and store the Salesrep so we can now track how they are doing. So when the user enters an Order they would select a Dealer(Customer) and next select a Salesrep. That list must be limited to Salesreps for that dealer. Does that change what you are suggesting. As I read Tim's response I realized there may be some confusion. This is my first solution from the orginal message. tblCustomers tblSalesReps tblOrders -------------- --------------- ----------- - CustomerID ------------ SalesRepID ---------- OrderID CustomerID SalesRepID And this is the second solution. Which gives me referentail integrity throughout so that if a Sales rep's data is changed, I.E name change or something it is casscaded down. tblSalesReps tblCustomers tblOrders -------------- --------------- ----------- - SalesRepID ------------ CustomerID ---------- OrderID CustomerID ---------------------------------------- CustomerID SalesRepID I guess the first question is; Does any of this new information change your suggestions? If so what would you advise. If not maybe you can explain a bit more why your response would work in this situation. The second question is; Are there any drawbacks to doing the second way. I know what I learned in school leads me to think the first way, but my gut says the second way is not completely wrong? Thanks for all your help, this is great! -Rick "John Vinson" wrote: On Fri, 23 Jul 2004 08:48:02 -0700, "Rick Vooys" wrote: What we have currently is tblCustomers and tblOrders. Each customer can have many or none orders. Now we want to add a table for Sales Reps. Each Customer can have one or many Sales Reps. Each Order must have one Sales Rep. My first impulse is to slide a table between tblCustomers and tblOrders, tblSalesReps. I know this is the right way to do it, but since this will involve almost every part of the database being changed I am wary of jumping into it. I am investigating the users needs more to better understand the problem, but what are the advantages to this approach and what are the disadvantages to this approach. I'd suggest that since "each Customer can have one or many Sales Reps" and "each Order must have one Sales Rep" - and, I'm sure, "each Sales Rep will have one or many Orders" - that you link Orders to Sales Reps; if you need to link a sales rep to a customer, you can do so via that customer's Orders. The Orders table here is functioning (in part) as the "resolver table" for the many to many link between Customers and Reps. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help with query or table setup | Kerrie | General Discussion | 0 | July 1st, 2004 10:33 PM |
Help with query or table setup | Shanin | General Discussion | 0 | July 1st, 2004 10:33 PM |
Image Control Table | John Gavin | General Discussion | 3 | June 28th, 2004 04:21 AM |
Office XP | Ed Lester | Setup, Installing & Configuration | 1 | May 27th, 2004 09:30 AM |
Table design for a booking system | Brian C | Database Design | 2 | April 27th, 2004 03:11 AM |