The main problem here is the lack of understanding on the n-tier architecture. Here is a diagram to understand the layers.
- nt-graph.png (50.95 KiB) Viewed 12031 times
When you are designing software, you need to be aware of the concepts well. Without describing further, I'll give you a quick idea on how it should be designed.
The first two layers (Presentation and Business logic) are implemented in php according to your application. The database is only used as the data store mostly. However it is important to designed the database in a way that all tables are 3rd order normalized by taking data integrity (Entity, Referential and Domain) under deep consideration.
All user specific verifications must be done in php as it is a part of business logic with the support of data in the database.
For your problem, you can have tables as follows. (Note that all tables are normalized so data are not redundant).
Store_Description
Code: Select all
| Store ID | Description | Location | Telephone | Manager |
Store_Employee
Code: Select all
| Store ID | Employee ID | Assigned Date/Time | Assigned by | Is Active? |
Store_Inventory
Code: Select all
| Store ID | Item Code | Qty | Reorder Level |
On the 2nd table, whenever an employee is assigned "Is Active" field will be set to TRUE. When you resign the employee, it will be set to FALSE. In this way you can keep a track of employee rather than just deleting the record.
Okay, now when you have a perfectly designed system, you can address the issues. Let's think we have two stores. So our
Store_Description table would be as follows.
Store_Description
Code: Select all
| Store ID | Description | Location | Telephone | Manager |
------------------------------------------------------------------------------------------------------
| 00001 | ABC Stores | Colombo | 0112 675432 | Herath |
| 00002 | XYZ Sores | Nugegoda | 0112 342123 | Viddz |
------------------------------------------------------------------------------------------------------
Say we have 3 employees; One is working at ABC and two at XYZ. So our table would be,
Store_Employee
Code: Select all
| Store ID | Employee ID | Assigned Date/Time | Assigned by | Is Active? |
---------------------------------------------------------------------------------------------
| 00001 | 11111 | 2012/05/26 10:50AM | Admin | TRUE |
| 00002 | 11112 | 2012/05/27 11:50AM | Admin | TRUE |
| 00002 | 11113 | 2012/05/28 12:50AM | Admin | TRUE |
---------------------------------------------------------------------------------------------
Okay, so far so good.
Now think the employee with ID 11111 has logged in to the system and going to look at the store ID 00001. From the php code, you execute a query as "SELECT * FROM Store_Employee WHERE Store_ID = '00001' AND Employee_ID = '11111'". You get a not null record. That means this employee can access the store with ID 00001. Okay, now say he is going to check the other store. You execute the same SQL with a change to Store_ID. You will get a null recordset (that mean no records to match that criteria). In that case show a message to the user, "You don't have privileges to access this store".
SQL checking part belongs to business logic where as the user entry and message showing part belong to presentation. Are you clear now or am I talking about a complete different problem?