Inventory Management Database Design

[sajitk]

Friends,

I am trying to desing an inventory management system for my office. I have designed the database. the details of each table are given below:

Emp_Mast
E_Code, Text, PK
Emp_Name, Text

Item_Mas
Item_Code, Text, PK
Item_Desc, Text
Item_Brand, Text
Item_Re-orderlevel, Numeric

Vendor_Mas
Vendor_Code, Text, PK
Vendor_Name, Text
Vendor_Add, Text
Vendor_City, Text
Vendor_State, Text
Vendor_Country, Text

Issue_Mas
Item_Code, Text, FK
Item_Desc, Text
Issue_To, Text (Issued to which employee)
Issued_Qty, Numeric


Recd_Stk
Item_Code FK, Text
Item_Desc, Text
Recd_Stock, Numeric
Date_of receipt, Date
Vendor_ID FK, Text

Can anyone suggest whether the design is correct. If no, then please suggest the changes.

Thanking you in advance.
Sajit

[jeffstl]

The first and most obvious thing is that you should be doing look ups for your Item_Desc and Item_Code based on Item_Mas instead of duplicating the Desc and Code in those tables.

The main reason for this is that if an ITem Desc or Item Code changes in the future, you only have to make that change in ONE place in the design (one record). Rather then making that change in 3 different tables for every single instance of that particular item.

Issue_Mas
Item_Mas - use this to look up code\desc
Issue_To, Text (Issued to which employee)
Issued_Qty, Numeric


Recd_Stk
Item_Mas - - use this to look up code\desc
Recd_Stock, Numeric
Date_of receipt, Date
Vendor_ID FK, Text

At first glance besides that it looks fine, but I don't know what you wont the application to accomplish, or what the exact specifications are for each of these columns

[sajitk]

thanks Jeffstl,

I would be making those changes in the database as suggested by you.

Just to give a brief on wat this application is supposed to do. We basically need to track stationary received from various vendors and the issuing of the same to staff members.

At the end of the month we sud be able to see the amount of stock in hand for each stock and place an order for the stock whch has reached its Re-Order level. this particular application just tracks the receipt and issue of stationary.

Hpe this gives you a background info on the application.

I wud be disturbing you while making ths application. hpe this is ok with you.

sajit

The first and most obvious thing is that you should be doing look ups for your Item_Desc and Item_Code based on Item_Mas instead of duplicating the Desc and Code in those tables.

The main reason for this is that if an ITem Desc or Item Code changes in the future, you only have to make that change in ONE place in the design (one record). Rather then making that change in 3 different tables for every single instance of that particular item.

Issue_Mas
Item_Mas - use this to look up code\desc
Issue_To, Text (Issued to which employee)
Issued_Qty, Numeric


Recd_Stk
Item_Mas - - use this to look up code\desc
Recd_Stock, Numeric
Date_of receipt, Date
Vendor_ID FK, Text

At first glance besides that it looks fine, but I don't know what you wont the application to accomplish, or what the exact specifications are for each of these columns

[sajitk]

I have made the changes suggested by you.

i have a doubt. now suppose I am in the Issue_Frm or for that matter I am in the Recd_Frm, if the user selects the Item_Code from the drop down list, the item_Desc should come automatically from the item_master for that particular Item_code.

can fhis be done...??? if yes, then how.

thanking you in advance.
sajit

[jeffstl]

I have made the changes suggested by you.

i have a doubt. now suppose I am in the Issue_Frm or for that matter I am in the Recd_Frm, if the user selects the Item_Code from the drop down list, the item_Desc should come automatically from the item_master for that particular Item_code.

can fhis be done...??? if yes, then how.

thanking you in advance.
sajit

Sorry. I did not realize you had replied again.

What you are talking about is exactly why you would set up this database in this way. Because for instances like the one you describe, you will always be getting the data from the same location, so that your database does not have redundant data.

In other words, suppose in the future an Item Description changes. You would then have to go out to every single record of the database where you store Item_Desc and change it. Whereas if you do as I described above you only have to change it in 1 place (In the look up table that has code\desc only)

What you need to do is a look up whenver you get a Code. I would write a function that you can use over and over again that accepts the parameter of the code and returns the desc.

Function GetDesc(MyPassedCode)
     dim MySQL
     MySQL = "Select Item_Desc from ItemTable where Item_Code = " & MyPassedCode & ""
     MyRS.Open MySQL,DataConn,3,3
     if MyRS.Recordcount > 0 then
          GetDesc = MyRS("Item_Desc")
     else
          GetDesc = ""
     end if
    MyRS.Close
End Function

[sajitk]

OK Got your point.

I will do the needful.

Sajit

Sorry. I did not realize you had replied again.

What you are talking about is exactly why you would set up this database in this way. Because for instances like the one you describe, you will always be getting the data from the same location, so that your database does not have redundant data.

In other words, suppose in the future an Item Description changes. You would then have to go out to every single record of the database where you store Item_Desc and change it. Whereas if you do as I described above you only have to change it in 1 place (In the look up table that has code\desc only)

What you need to do is a look up whenver you get a Code. I would write a function that you can use over and over again that accepts the parameter of the code and returns the desc.

Function GetDesc(MyPassedCode)
     dim MySQL
     MySQL = "Select Item_Desc from ItemTable where Item_Code = " & MyPassedCode & ""
     MyRS.Open MySQL,DataConn,3,3
     if MyRS.Recordcount > 0 then
          GetDesc = MyRS("Item_Desc")
     else
          GetDesc = ""
     end if
    MyRS.Close
End Function