Results 1 to 9 of 9
  1. #1
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119

    Problem with database design

    Hi friend,
    i have a problem related to the design of a DB, and i hope someone will help me out.

    It's all about a company (let's call it COMODO) that sales products. COMODO has 4 departments.
    Each department has its own stock which is supplied by the main stock of the company (like a principale stock that supplies sub-stocks).

    We need to keep records of the main stock's inventory of COMODO. And we also need to keep records of the stock of each department.

    Sales transactions must be recorded. Inventory in-flow and out-flow (for the main stock and the departments' stocks) must also be recorded.

    For every department we need to have a report displaying the information:
    - item name, item stock before sale, total quantity sold, and item stock after sale for every product available in the department stock.

    We also need to have a more general report displaying:
    - Department name, item name, item Stock before sale, Total quantity sold , and item stock after sale per department for every item. And group it by department.

    What could possibly be the best design to have everything working perfectly? I know some part should be handled
    at the frontend application level, but i believe a good design will make think smoother.

  2. #2
    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
    For consideration:

    For each department identify the dept and all of its Product transactions.
    Inflows of Products are positive, outflows are negative.
    If you sum the transaction quantities, by type and Product for a time period, you get the Total for the Company.
    This seems to be the basis for the table design. But it is a starting point--you can test thi model with test data and your business rules and adjust as necessary.
    You will have to identify and include the CurrentProductPrice (in the Product table) and the ProductSalePrice in the ProductTrans table.

    Click image for larger version. 

Name:	ProductTransAndInventory_draft.png 
Views:	33 
Size:	17.1 KB 
ID:	31511




    These are the outputs to be derived from the database:
    Outputs:
    For every department we need to have a report displaying the information:
    - item name, item stock before sale, total quantity sold, and item stock after sale for every product available in the department stock.

    We also need to have a more general report displaying:
    - Department name, item name, item Stock before sale, Total quantity sold , and item stock after sale per department for every item. And group it by department.

    Good luck with your project.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    The easiest way will be to add a field p.e. Store into ProductTrans table. Store values are for company's main store (there can be several of them, or one) and for every department's store. When the product is moved form main store to another one, you have 2 records in ProductTrans table with one negative quantity for main store, and according positive quantity for department store. The sum of this movement will be 0. And vice versa for moving product from department store to main one.

    You can calculate a stock for product separately for each store by this design.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm thinking the last proposal is good as long as there won't be any direct sales from the main store. If there will be, a Loan- and Loan+ in a Transaction field could distinguish between sales and transfers from main to sub stocks.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Micron View Post
    I'm thinking the last proposal is good as long as there won't be any direct sales from the main store.
    For this a remedy is on place - TransactionType. For OP's needs I think hard-coding the forms behavior depending depending on transaction type is a way to go. I. e all sales, purchases, inventory corrections, movings into production and returns from production are one-row transactions. All movings fom one store to another are two-row transactions.

    For more sophisticated application, you have to determine this in TransactionTypes table as transaction type property.

    For most of ERP systems, the quantities of incoming and outgoing goods are registered in main store. Monetary values are calculated and invoices/orders are registered in bookkeeping module.

  6. #6
    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
    Perhaps we should await a "status" response from ezybusy who has been online throughout these posts.

  7. #7
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    @orange
    Thank you for proposing a solution. Sorry for the late reply i was a bit busy. I have not tried your solution yet because I have some questions:

    - what is the actual use of the field ProdTransUnit?
    - How will the inventory side look like in terms of relationship with the other tables? Because we have two types of inventories, One for the Main stock and another to deal with department stocks.

    Stock outflow for sale transaction is deducted from department stock. And we only deduct Main stock when supplying a department's stock.

  8. #8
    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
    As indicted in the diagram before, ProdTransUnit indicates whether you are talking about per Item(Each), or by Dozen, or Lbs, or Pallets etc. It's basically your unit of measure for the quantity of Item(s) in a transaction.

    It wasn't clear to me if items were managed by Department, and the sum of all department transactions equated to Company transactions.
    If you have more details, or if you use the sample as a starting point, I'm sure you'll identify additional requirements.

    From a logical model view, you could include a DeptType in Department and identify MainStore as Main.

    If you want to identify Main as a separate entity, then perhaps Micron's LoanIN/LoanOut could apply.

  9. #9
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    @Orange.
    Alright, I will try your proposed design and post back.
    Thanks again.

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

Similar Threads

  1. Database design relationship problem
    By cysklement in forum Access
    Replies: 4
    Last Post: 12-04-2014, 08:47 AM
  2. Small Database Design Problem
    By PalmerR in forum Access
    Replies: 4
    Last Post: 07-30-2014, 11:53 AM
  3. Replies: 3
    Last Post: 01-06-2012, 03:30 PM
  4. problem while saving database design
    By Ramya in forum Database Design
    Replies: 1
    Last Post: 08-02-2011, 07:39 AM
  5. Database Design Problem
    By Kurth in forum Access
    Replies: 0
    Last Post: 08-14-2008, 04:09 AM

Tags for this Thread

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