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

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

"=?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

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
.

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

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

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

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