none
Inventory Control Database design RRS feed

  • General discussion

  • I design a database for inventory control. The company has three warehouses

    the first warehouse for materials used to produce the final products

    the second warehouse for tools that the company uses fo machines maintenance

    The third warehouse for final products (with two locations)

    I think i should have two products tables one for materials and tools as I purchase them from suppliers

    and another table for final products that the company manufacture

    my problem that i need to give a permission to an employee when he login to the system he gets specific

    warehouse and its related products 

    I really need help to handle this within the database design

    I really appreciate any help


    Tuesday, May 14, 2013 9:53 PM

All replies

  • Try Section 12 (Inventory) of the Data Models from Database Answers as the starting point for your database.


    Van Dinh

    Tuesday, May 14, 2013 10:17 PM
  • hi, 

    I designed the most of the database tables and set the relationship

    the problem that i need to give a permission to an employee when he login to the system he gets specific

    warehouse and its related products 

    how can I handle that within the design

    any help would be appreciated

    Tuesday, May 14, 2013 10:37 PM
  • Personally, I think you should consider carefully with the set-up:

    >>I think i should have two products tables one for materials and tools as I purchase them from suppliers and another table for final products that the company manufacture<<

    If you are talking about warehousing concept, you really should only have [Item]s stored in [Warehouse]s (the 2 important Entities are in the square brackets).  If you have 2 Tables to store [Item] data, I think you will have a lot of problems in setting up the linking as well as data processing/manipulation in the future.

    You can assign the default Warehouse to each user in the Table [User] and create your own Login Form to identify the user and hence his/her Warehouse. 

    However, this is identification and not security. Access never had row-level security and furthermore, Access ULS (User-Level Security) has been deprecated in later versions of Access.  Thus, if it were me, I would not depend on Access ULS.

    If you actually want Security (i.e. preventing users from viewing/editing certain sets of data), then it may be better to use another database engine with Security such as SQL Server (including the free Express version) as the Back-End database with Access GIU as the client Front-End.


    Van Dinh


    • Edited by Van Dinh Tuesday, May 14, 2013 11:22 PM
    Tuesday, May 14, 2013 10:58 PM
  • >>If you are talking about warehousing concept, you really should only have [Item]s stored in [Warehouse]s (the 2 important Entities are in the square brackets).  If you have 2 Tables to store [Item] data, I think you will have a lot of problems in setting up the linking as well as data processing/manipulation in the future<<

    I will explain what I need to do in steps because I really need help and I am afraid of the problems in designing this database.

    1- the materials warehouse receives the materials from suppliers and delivers them to the production department 

    2- the tools warehouse receives the materials from suppliers and delivers them to the production department as well

    so those two warehouse gets tools and materials from suppliers and delivers them to production 

    3- the final products warehouse receives final products from production and delivers them to customers 

    so, the logic here would be like that:

    1- we get Purchase orders from customers and submit a work order for production and submit the order to final products warehouse

    2- the production submit a material request to materials warehouse and warehouse post the quantity to the production and the same goes for tools

    so, what do you think about creating two separate products tables

    thank you very much

    I really appreciate your help

    Tuesday, May 14, 2013 11:30 PM
  • I think you will have to combine the 3 models (1, 2 and 14) in Section 12 (Inventory) of the database models from Database Answers whose link I posted earlier.

    IMHO, you have one Entity [Item] of 3 different Sub-Types, namely: Materials, Tools and (finished) Products and not 3 Entities.  Clearly, the [Warehouse] is an Entity on its own plus possibly, the Entity [StorageLocation] in the [Warehouse].

    However, please note that you are in the best position to decide the Enitities you need for your database application since you should have identified your database requirements by now and then apply your knowledge on the Relational Database Design Theory (RDDT) to work out the required Entities from the requirements.  We are not in a position to do so since we have not done detailed identification of database requirements.

    If you are not sure of RDDT and Database Normalization, you may want to refresh your memory on RDDT and DB Normalization.


    Van Dinh

    Tuesday, May 14, 2013 11:46 PM