MS Access relationship setting problem

Question

The instruction is to setting relationship in MS access. There are 3 tables that are list for salesperson, customers, and oder. Each customers are assigned with 1 sales person where staff id is the primary key and linked to the customer list. Im not sure whether it should set as cascade update only? If it is required to delete the salesperson data when they quits and reassign a new one to take over.

Answer 1

You do not want cascade deletes on! Let's say John Doe is a salesperson who leaves the company. With cascade deletes on, deleting John Doe's record would delete ALL the customer records who are assigned to John! If a new salesperson takes over, and IF you do not need to retain any history of who John Doe serviced, then you could simply replace John Doe's name in the salesperson table with the new Salesperson ID (assuming they weren't already listed). Keep in mind that doing this will remove ALL traces of John ever being employed - so this is a simple solution that only works if you don't care about history! If John's customers were going to be taken over by an existing salesperson, you would create an Update Query that would replace John's Salesperson ID in the customer table with the new Salesperson ID. Once again, that destroys the history - John would still be listed as a salesperson but not assigned to anyone.

Before doing any of that, however, ask whether a history table might be needed! Would anyone ever have to know who John serviced in the past? If so, maybe another table called something like SalesPerson Assignment should be added, which would have the Customer ID, the Salesperson ID, a Date Assigned, and a Date Ended field! In that case, you would NEVER simply make changes to the name fields in the Salesperson table.

I run into this scenario in many of my databases, and I believe the best way to handle this is to add the Salesperson ID to the order table. When a new order is created, the default value for that field will be whatever Salesperson ID is currently assigned at the Customer level. Then, when a customer gets assigned to someone new, all you have to do is change the ID in the Customer table - but you retain the history of orders that the terminated salesperson handled!