How to assign members for shop in a inventory control system

[viddz]

In my inventory control system I have put members table and separate table for each and every shop & warehouse. my problem is how do assign employee for a shop. Assigned employee to a particular shop or warehouse should only be able to manipulate stock of the that shop only. And also admin is responsible for assigning and changing members working place(shop)How do i do this?

[Herath]

Consider the relationship "Employ WORKS AT Warehouse". If this is a one-to-one relationship, you could simply put the warehouse ID in the employee table.

Code: Select all

CREATE TABLE Warehouse(
w_id INTEGER PRIMARY KEY,
name varchar(10),
address varchar(10))

CREATE TABLE Employee(
e_id INTEGER PRIMARY KEY,
name VARCHAR(10),
manages INTEGER REFERENCES Warehouse(w_id))

The permission to manage only assigned shops will have to be implemented in the software logic level. As far as I know, it is not possible to fix such permissions at the data level.

[viddz]

Thanks.
hmm Actually my problem is how to grant access to users only to the relevant table. In this system i have assigned 4 types of users and i have granted access them by using session.
eq for admin,

Code: Select all

if(!isset($_SESSION['SESS_LOGIN']) || $_SESSION['SESS_TYPE'] !='admin')  
 {                                                                                
 
  echo "pls log as ADMINISTRATOR to do this"; 
 }
else
{
some code here...............
}

hope now its clear wat i want to do

[Herath]

The way you are handling it in the PHP code is correct. If the user permission level is "admin", it is possible to grant all the powers over the system and you can do the relavent if otherwise.

Code: Select all

if(!isset($_SESSION['SESS_LOGIN']) || $_SESSION['SESS_TYPE'] !='admin') 
{                                                                               

 // echo "pls log as ADMINISTRATOR to do this";
  Show administrator interface
  Manage the system 
}
else if (user is an employee)
{
   Show employee interface
   Manage warehouse (ie, control stockes, check stocks etc)

}

If you are wondering about an employee from a different warehouse managing stuff of another warehouse, you could prevent that by verifying the w_id of that employee before allowing acess.
It is a little bit hard to answer without knowing your knowledge about Software Designing. I hope that I have gotten through what I am trying to say.
Let's see if anyone else is going to show up with help.

[Saman]

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 13721 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?

[viddz]

wow saman it is crystal clear. That is exactly what i want.
Thanks a lot saman

Another problem
I want to take monthly/weekly reports. but how i update Store_Inventory is like this

Code: Select all

1.UPDATE item_table SET qty=qty+$transaction_amount WHERE Item Code=A00001
                                  OR
2.UPDATE item_table SET qty=qty-$transaction_amount WHERE Item Code=A00001

In this case Iam not storing $update variable no. So i Have decided to create new table for storing those updates like this

Code: Select all

|    Item Code       |     transaction_type   |    Date/time     |    transaction_amount 

transaction_type can be issue or receive. If issue iam executing query no 2. if receive query no 1. by using this table iam taking the reports. Is it a correct way of doing this or not. Ur comments are highly appreciated.

Btw i really like the way you explain and effort you have put here to teach us things. Iam learning lot from you. I have theory knowledge but the problem is when it comes to practical environment i dont knw how to use it. In that case you helped me lot. Thank you again

[Saman]

transaction_type can be issue or receive. If issue iam executing query no 2. if receive query no 1.

This is correct.

by using this table iam taking the reports. Is it a correct way of doing this or not.

This is also correct and must be done as well. These types of tables are called log tables where we store log records for tracking, reporting, etc...

However, in a standard inventory system, there are notes called GRN (Good Receive Note) and GIN (Good Issue Note). GRN is associated with Purchase invoice and GIN is associated with Sales Invoice. Purchase and Sales invoices are entered on the accounting system where those are then entered as GRN and GIN to the stock when the stock department actually receive/send the physical stock.

GRN and GIN are entered to the database as log records. After entering a whole GRN or GIN, there will be a "Process" button where actual stock adjustment will happen after pressing that.

In simple terms,
GRN -> Process -> Add to stock
GIN -> Process -> Reduce from stock

This is the process of an inventory control system.

[viddz]

GRN and GIN are entered to the database as log records. After entering a whole GRN or GIN, there will be a "Process" button where actual stock adjustment will happen after pressing that.

In simple terms,
GRN -> Process -> Add to stock
GIN -> Process -> Reduce from stock

1. Here you have mentioned about process button. Is it GRN forms submit button or a separate one. when GRN/GIN submit do we have to update Store_Inventory table without acknowledging user(like system_log record adding).

2. Think there are 12-10 stores. admin would be able manipulate all stores. So do we have to add 12-10 records in Store_Employee table or is it any special way for admins to access all stores?

[Saman]

1. Here you have mentioned about process button. Is it GRN forms submit button or a separate one. when GRN/GIN submit do we have to update Store_Inventory table without acknowledging user(like system_log record adding).

It's like this. You will have two button on the GRN/GIN form. You might name them as "Add" and "Process". "Add" is for adding individual items. Say you have 10 items on the GRN. You simply add all 10 items to the GRN which is added one after another to a table view. The records will be saved to table which can be later used a log.
Once you are done with entering items, you can then process these to stock. That the last step of the GRN/GIN entry. Once you do this, all items are added to the stock and the GRN entry screen need to exist.
GRN must have two tables. One is to store header record and the other to store items. On the header record, you need to add who is entering the this GRN/GIN.

GRN_Header

Code: Select all

|  GRN_Number |   GRN_Date  |    User ID   |  Store ID  |   Remarks |

GRN_Items

Code: Select all

|  GRN_Number |   Item_Code  |    Qty    |  Expiry Date  |   Remarks  |

2. Think there are 12-10 stores. admin would be able manipulate all stores. So do we have to add 12-10 records in Store_Employee table or is it any special way for admins to access all stores?

Say you need to appoint another person as admin. How you are going to do that? Modify your code? Also, if you treat admin specially, then you are breaking the design. So be sure to add 12 records for admin for each and every store.
You can also use a bit pattern to maintain the privileges. For example:

User Table

Code: Select all

|  Username |  Password  |   GRN   |   GIN   |   RRN    |  SRN   |  Reports |
|  myname    |   mypass   |     34    |     34   |     34    |    34   |       34    |

RRN - Return receipt note (exactly as the process of GRN but these are return items from customers)
SRN - Supplier return note (exactly as the process of GIN but these are return items to supplier)

Think that each bit represent each store.
34 in binary is 0000 0000 0010 0010. So it is that this user has access to 2nd and 6th store.

That is just an example just to illustrate how these things are handled properly.

[viddz]

problem 1 solved.
But i am still having doubts how to access tables using binary representation. Concept is crystal clear but dont have an idea how to implement this thing.
lets consider ur example
34 in binary is 0000 0000 0010 0010. So it is that this user has access to 2nd and 6th store

my problem is how system identifies 34 is 6 and 2 and allow users to manipulate that stores?