Results 1 to 3 of 3
  1. #1
    gmyt72 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    1

    Warehouse Database - Best Way to Approach

    Hello everyone,



    My question overlapped with many sub-forums but it is mostly one regarding organization and theory so I believe this is the right place to ask.

    I am currently interning in a commodities firm where we deal with a lot of warehouses where we store material. We deal with 3 types of warehouses:


    • Ports - facilities that are next to ports and discharge large seagoing vessels.
    • Riverports - facilities in rivers that discharge barges.
    • Inland - facilities with no waterways nearby, which can only handle material from trucks and/or rail cars.


    Each of these facilities offers us two categories of rates; rent and handling.


    • Rent - the cost to store material in the warehouse (usually in $/short ton/month). Different for inside and outside storage.
    • Handling - the cost to handle material in/out of a warehouse (usually in $/short ton).


    Another categorization is the different shapes of material that we handle. Depending on what shape the material has, warehouses and stevedores will charge different prices to store/handle it.

    Finally, different rail-carriers serve different facilities, something which I would also like to keep track of.

    What I would like to do is create a database with all our warehouse information, so that it can be referred to and amended in the most user friendly way possible (forms, reports etc.). I currently have most of the data in Excel format but it is very had to keep track of it, update all tables simultaneously when adding a new facility etc. This is why I thought Access would be the best solution.

    This database's primary purpose would be to create reports that can be given to traders, outlining the cost to handle and store material in a particular facility. This would mean that functionality to find the cheapest rate in an area would be needed etc. Export to Excel would also be very useful.

    I would very much appreciate as much help as I can get on the matter. I have attached a sample database for your information; in design view I have a description of what the variables in the tables are. Please check the relationships for a clearer understanding of how these fields are intertwined.

    Sample Warehouse Database.accdb

    Thank you all very much for going through the long post and I hope you can help me out.

    Best regards,

    gmyt72

  2. #2
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742

    Quick overview of likely design

    Real quick overview off the top of my head. I'm thinking there is a problem with your definitions. A single port could have all types of facilities (ocean, river, rail and trucking) or any combination.

    From a normalized point of view, A Facility would have a key and its various descriptions (location, GPS, name, mailing address, etc).

    A Transit Mode record would exist for each facility for each mode of travel,

    ... possibly keeping different Rail carriers each as a different record on this same or a different table, in which case different truck carriers would also have different records in the same place

    A Shape table would have its description

    Rate records would have the shape, action, and effective dates (since the rates will change over time, you want to treat this as a slowly changing dimension. You will probably want the rates to have a start and end date.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Hi Dal, Welcome back -- haven't seen you in the forum for quite a while.


    gmyt72,

    I think it is great that you have put some definitions/descriptions with your proposed fields. I agree with Dal that you're off to a good start, but may need to do some research and adjustment on table design and relationships (Normalization). Facilities

    It seems you have a number of Warehouse in different locations that offer a variety of services. It can be a worthwhile effort to write 30000 ft overview of the business processes to be supported by your database. Gradually add more detail and bounce it off colleagues (and this forum) to make sure it is understood. Avoid jargon. The point is to use a complete description of the business and create a data model showing proposed tables and relationships. Then with some test data and typical scenarios, test the scenarios against the model. Can you get the info you want from the current model design. If you can't, then resolve the issue -is it bad data, missing table, bad relationship, process has more logic than model..... adjust the cause and repeat until the model supports the scenarios. Get others involved in the testing of the model. Once it works to your satisfaction, your model becomes the blueprint for your database tables and relationships.

    The data model can be an excellent tool for communications, training, maintenance....

    You show rates for <30, 30-60 >61 etc---How do you plan to keep these current?
    If you have any historical records and use that rate structure, you will lose historical records --UNLESS-- you store the RateAtTheTime with associated records.

    Good luck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 09-18-2015, 02:20 PM
  2. Warehouse
    By vugar in forum Sample Databases
    Replies: 3
    Last Post: 05-01-2014, 06:36 AM
  3. Warehouse database
    By vugar in forum Sample Databases
    Replies: 6
    Last Post: 04-23-2014, 09:52 AM
  4. Freezer Warehouse Tracking Database
    By csdrip in forum Database Design
    Replies: 1
    Last Post: 03-06-2014, 10:27 AM
  5. Replies: 4
    Last Post: 01-30-2014, 12:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums