Managing Ownership
Date: 7 Sep 2004 14:46:35 -0700
Message-ID: <7eb017e9.0409071346.54ac2b7f_at_posting.google.com>
I am trying to work out a multilevel ownership issue, and I would like some outside input please.
I am writing an application for a nationwide sales force of reps that call on accounts. They divide the country into 6 regions, and each region into territories, usually around 10 depending on the size of the region. Each territory contains 10-20 accounts.
On the user side I have corporate users who monitor the whole country, regional managers who monitor all the territories in a region, and salespeople who service the accounts in a territory.
I am trying to come up with a system for tracking who gets to see what accounts. Currently each account is given a 6-digit number which consists of a 4-digit, 0-padded region number and a 2-digit territory number concatenated, so an account in territory 2 of region 3 would have a number of 000302. Then there is a table in the database with a userID and territory field. The salesperson's territory would be 000302, the regional manager's would be 0003, and a corporate user's would be 00. Then I would query the accounts by selecting all records from accounts having Account_Territory starting like the users territory. SELECT * FROM ACCOUNTS WHERE Territory LIKE '0003%' in the case of the regional manager for region 3, which would return all accounts in 000301, 000302, ... It could be possible for a single user to be covering two regions or territories in reality, but there is no way to show that here.
I am not thrilled with this solution and want to change it. The best alternative I have come up with on my own is to change the user table to include userID and userType columns and then have a related table or tables that show what regions or territories that person should have access to.
Something like:
If UserType = "Regional Manager" Then
SELECT * FROM Accounts WHERE Region IN (SELECT Regions FROM AllowedRegions WHERE UserID = <My User ID>)
If UserType = "Sales Rep" Then
SELECT * FROM Accounts WHERE Region IN (SELECT Regions FROM AllowedRegions WHERE UserID = <My User ID>) AND Territory IN (SELECT Territories FROM AllowedTerritories WHERE UserID = <My User ID>)
If UserType = "Corporate" Then
SELECT * FROM Accounts
Does this seem like a valid solution to this problem? Any other possibilities I am not seeing? Received on Tue Sep 07 2004 - 23:46:35 CEST