So I have a database with 30some data and lookup tables. Each table contains various info, but each table also contains two fields that tie everything together; CustomerID and GeneratorID. This DB is used to track waste containers for various clients. Each client/Customer has an ID and since each client/Customer can have waste generated at various locations each client/Customer can have multiple GeneratorIDs assigned. For example:
Client: ABC Waste
CustomerID: 01
GeneratorID: 01
GeneratorID: 02
Client: XYZ Waste
CustomerID: 02
GeneratorID: 03
GeneratorID: 04
GeneratorID: 05
In my users table I have the normal User data and a UserID & Password as well as the CustomerID for whatever client/Customer the user works for. I also have a Permissions field as an interger so I can have different levels or Groups of users that have permission to view select tables and to add, edit, etc. That's the easy part!
What I really want to do is for the database to not only use the GroupID(Permissions) but to also use the CustomerID. So if user Bobbie Jo from ABC Waste logs in as a Permission "100" she sees all the tables and can add, edit, etc BUT only that data that is for Client ABC Waste (CustomerID: 01).
My work around is to create seperate VIEWS for each client/Customer, but that can become a management nightmare as more client/Customers are added to the database. Surely, there must be some logical way to solve my delemma, but I can't seem to find one. Can one of you smart people out there lend me a hand please?
DouglasB
