Search code examples
mysqldatabaseinventory-management

is there a MYSQL tables schema for storing raw material, intermediate and final products?

I am creating an inventory database, I have some raw material which is converted to an intermediate product and then it is converted to a final product. eg.

100 threads are used to make 1 cloth. 3 cloths are used to make 1 shirt.

Hence, If I have 300 thread then I can make 1 shirt. Like this, there are several levels of intermediate products. how can I store them in the MYSQL table and convert from X intermediate level to Y?

I can change the data structure used if you have a better suggestion.

Solution

  • This is not how accounting works. If you want to track the manufacturing then you have to store the articles (either raw materials or final products) AND the fact of their transformation (the document).

    This can be solved with 5 tables:

    • articles (raw materials, final products, type of labor involved)
    • accounts (the warehouse, the workshop)
    • transactions header (the documents on whose basis you produce an intermediate or final product) - here you specify date, issuer, document status (draft, valid, cancelled)
    • transactions body left side - here you specify source account and source article (may have multiple rows) plus quantity and measure
    • transactions body right side - here you specify destination account and destination article (may have multiple rows) plus quantity and measure

    Each transaction has left and right side - source and destination. You will have 2 kind of transactions:

    • taking raw materials from the warehouse + investing some labor to manufacture something
    • putting the resulting intermediate or final product back into warehouse

    For type A on the left side you specify the raw materials and the labor that are being used - and on the right side you specify the resulting intermediate or final product. Accounts will be warehouse as source and workshop as destination.

    For type B you specify the same product on both sides of the transaction - but the accounts will be workshop as source and warehouse as destination.

    Then, when you sell the products - you will have another transaction to get them out of the warehouse. And of course you should also add some materials to the warehouse from time to time - or it will get empty very soon (but these are out of the scope of your question).