locked
Designing stock tables RRS feed

  • Question

  • Hi, 

    I am designing for a sales application, OLTP database and I need to be sure that I am designing correctly for stock. I am seeing it as one table that's all I need and want to know if I am right. I see it as something like (stock_id PK, item_id FK to "items.item_id", qty, expiry_date, etc...) . Of course there might be some other attributes to add but I want to know if this way is right(only one table)?

    conventions to take into account. 

    "warehouse" is the physical place the commodities resides in.

    "stock" is commodities that reside in the warehouse.

    The scenario is, commodities are getting received in the warehouse by an employee using a goods receipt document which is based on master-detail tables, 

    1- goods_receipt_hdr(document_hdr_id PK, document_date, employee_id FK to employee.employee_id).

    "document_date" is the date the document created and of course indicates the date commodites are purchased.

    2- goods_receipt_dtl(document_dtl_id PK, item_id FK to items_item_id, qty, expiry_date, document_hdr_id FK to "goods_receipt_hdr.document_hdr_id",....).

    after the document is created, the "STOCK" table is updated with the new items' qty so the quantity are accumulated.

    - An example. The company bought 50 pieces of item "1" today. a goods receipt is created as follows,

    - GOODS_RECEIPT_HDR:

    document_id= 1.

    document_date= '24/03/2019'.

    employee_id= 1.

    - GOODS_RECEIPT_DTL:

    document_dtl_id= 1.

    item_id= 1.

    qty= 50.

    expiry_date= '12/12/2022'

    document_hdr_id= 1.

    - after the document is created, an insert statement is issued to the stock table to add the newly bought commodity. 

    STOCK:

    stock_id= 1

    item_id = 1.

    qty = 50.

    expiry_date= '12/12/2022'.

    When the company buys another 50 pieces of the same item a week later, another goods receipt document is created and then an update statement is issued to STOCK table to add the new qty to the old qty.

    So stock_id number 1 becomes,

    STOCK:

    stock_id= 1.

    item_id= 1.

    qty= 100 (old 50 + new 50).

    expiry_date= '12/12/2022'.

    P.S.

    1- If the company buys the same item but with different expiry_date, a new insert statement is issued to the STOCK table as follows.

    STOCK_ID= 2.

    ITEM_ID=1.

    QTY= 50 or 60 or whatever.

    expiry_date= 11/11/2024.

    - When selling products, another document is created "goods_shipment". Then an update statement is issued to the STOCK table to subtract the sold qty. The items with the nearest expiry_date is dispatched first. i.e 2022 will be sold before 2024.

    So if the company is selling 10 pieces of item "1" with expiry_date '12/12/2022'. The stock table becomes, 

    STOCK:

    stock_id= 1.

    item_id= 1.

    qty= 90 (100 - 10).

    expiry_date= '12/12/2022'.

    Friday, March 22, 2019 12:12 PM

Answers

  • I dont know how exactly is your setup but I would also add a reference to location table inside stock in case the application has to deal with multiple warehouses/ offsite locations where stock is held

    I would also add purchase_date or inward_date as an attribute inside stock table to indicate when they were added to the system. It helps in lots of queries when stock audit is in place and also on calculations on KPIs like inventory turnover/sellthrough etc based on period.



    Sorry for the lack of information. My mistake! Please review the question again.

    if the STOCK table has a purchase date then how would you subtract from QTY when you sell an item? i.e

    stock_id, item_id, qty, purchase_date

    1, 1, 50, 24/03/2019

    2, 1, 50, 25/03/2019

    What should be happening when you try to sell 60 pieces? would you be issuing 2 update statements, one to update the 50 of 24/03/2019 to 0 and another one for updating the other 50 to 0?

    When you sell 60 quantities the transaction gets posted in sales table with quantity 60 and with sale date. You dont need to do anything Stock

    Even during final consolidation all it matters is what actual qty on stock is which you can get from SUM(Qty) from stock - SUM(Qty) from Sales

    Unless you've a batch concept (in which you should also have batch ID in your Stock and Sales tables) there's no need to map individual sales against particular Stock entry.

    In case you meant you've a single table for recording all stock activity then table may be better named Stock Ledger and Sales will be posted in the same table with -ve value with sale date to indicate sale transaction in that case.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, March 24, 2019 10:47 AM
  • >>> I am seeing it as one table

    I do not think you have one table, what is about dimension tables (Stocks, Items, Products and etc)

     >>>I see it as something like (stock_id, item_id, qty, expiry_date, etc.

    Means a  fact table , right? Yep, this is a way, just make sure  you have relation ship with Stocks table (stock_id)   and Product and etc...



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, March 24, 2019 5:23 AM

All replies

  • >>> I am seeing it as one table

    I do not think you have one table, what is about dimension tables (Stocks, Items, Products and etc)

     >>>I see it as something like (stock_id, item_id, qty, expiry_date, etc.

    Means a  fact table , right? Yep, this is a way, just make sure  you have relation ship with Stocks table (stock_id)   and Product and etc...



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, March 24, 2019 5:23 AM
  • I dont know how exactly is your setup but I would also add a reference to location table inside stock in case the application has to deal with multiple warehouses/ offsite locations where stock is held

    I would also add purchase_date or inward_date as an attribute inside stock table to indicate when they were added to the system. It helps in lots of queries when stock audit is in place and also on calculations on KPIs like inventory turnover/sellthrough etc based on period.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, March 24, 2019 6:31 AM
  • Sorry for the lack of information. My mistake! Please review the question again.
    Sunday, March 24, 2019 8:06 AM
  • What difference between goods qty and stock qty?

    In my opinion in order to build proper Stock design DWH you need Customer,Suppliers,Orders table...


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, March 24, 2019 8:20 AM
  • What difference between goods qty and stock qty?

    In my opinion in order to build proper Stock design DWH you need Customer,Suppliers,Orders table...


    If you are referring to goods_receipt.qty. Then it refers only to the newly bought qty. Whenever you buy a new qty, it's mentioned in a new document with it's qty. And goods_receipt is a document. STOCK is a table not a document.

    - Of course, I have customers, suppliers, orders, and other tables. I am just posting the tables that relates to my question.

    Sunday, March 24, 2019 8:26 AM
  • I dont know how exactly is your setup but I would also add a reference to location table inside stock in case the application has to deal with multiple warehouses/ offsite locations where stock is held

    I would also add purchase_date or inward_date as an attribute inside stock table to indicate when they were added to the system. It helps in lots of queries when stock audit is in place and also on calculations on KPIs like inventory turnover/sellthrough etc based on period.



    Sorry for the lack of information. My mistake! Please review the question again.

    if the STOCK table has a purchase date then how would you subtract from QTY when you sell an item? i.e

    stock_id, item_id, qty, purchase_date

    1, 1, 50, 24/03/2019

    2, 1, 50, 25/03/2019

    What should be happening when you try to sell 60 pieces? would you be issuing 2 update statements, one to update the 50 of 24/03/2019 to 0 and another one for updating the other 50 to 0?

    Sunday, March 24, 2019 10:30 AM
  • I dont know how exactly is your setup but I would also add a reference to location table inside stock in case the application has to deal with multiple warehouses/ offsite locations where stock is held

    I would also add purchase_date or inward_date as an attribute inside stock table to indicate when they were added to the system. It helps in lots of queries when stock audit is in place and also on calculations on KPIs like inventory turnover/sellthrough etc based on period.



    Sorry for the lack of information. My mistake! Please review the question again.

    if the STOCK table has a purchase date then how would you subtract from QTY when you sell an item? i.e

    stock_id, item_id, qty, purchase_date

    1, 1, 50, 24/03/2019

    2, 1, 50, 25/03/2019

    What should be happening when you try to sell 60 pieces? would you be issuing 2 update statements, one to update the 50 of 24/03/2019 to 0 and another one for updating the other 50 to 0?

    When you sell 60 quantities the transaction gets posted in sales table with quantity 60 and with sale date. You dont need to do anything Stock

    Even during final consolidation all it matters is what actual qty on stock is which you can get from SUM(Qty) from stock - SUM(Qty) from Sales

    Unless you've a batch concept (in which you should also have batch ID in your Stock and Sales tables) there's no need to map individual sales against particular Stock entry.

    In case you meant you've a single table for recording all stock activity then table may be better named Stock Ledger and Sales will be posted in the same table with -ve value with sale date to indicate sale transaction in that case.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, March 24, 2019 10:47 AM
  • Don't you think that this would have a very negative impact on performance? I already have tables(goods receipt tables) that mention when a specific item got received into the warehouse. And of course there are tables that indicates when an item got bought. Why would I add another "purchase_date" column in my stock table!

    2- What about reporting? Is it better to query a table that have few number of rows for each item in the warehouse or a table with a new row added every time you buy that specific item? 

    - Is it better to sum a column in a huge table and compare it to a sum of another column in another huge table than just query a table without any sums or comparing to other table?

    Sunday, March 24, 2019 12:34 PM
  • Eslam, have you made any progress with this issue?

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, August 8, 2019 6:15 AM
  • I ended to that it's different ways of implementation. Visakhk16 is one way to do it and I voted for it. And I can create i.e a table into which I insert any new bought qty of an item accumulated so that every item would have only one record with all bought qty. The same can go for any sold or returned items. That's another way of implementation I ended to. 
    Thursday, August 8, 2019 2:07 PM