Barcode inventory design – transaction or master

application-designdatabase-design

I am building a inventory software for mobile phone retailers. There are several things confuse me.

Ok, we have:

  1. Product
  2. Product Attributes
  3. Barcode
  4. Stock In Batch (Purchases/Return, etc)
  5. Stock Out Batch (Samples/Sales, etc)
  6. Stock Transfer Batch (Transfer stock from A to B warehouse)

I plan to do a transaction table for stock in/out/transfer.
So my product & attributes & barcode table should not have a qty field, right?

Below is my business rules

    1 product, 0 or many attributes; 
    1 attributes, 1 to many product

    1 product, 0 to many barcode;
    1 barcode, 1 product

    1 attributes, 0 to many barcode;
    1 barcode, 1 attributes

    1 barcode, 0 to many quantity

How many table should I created for this?

My table design is like this:

    - warehouse (warehouse id[pk])
    - product (product id[pk])
    - attributes (attribute id[pk])
    - product attributes (product id[pfk], attribute id[pfk])
    - barcode (barcode id[pk])

    - stock in batch (sib_id[pk], warehouse id[fk])
    - stock in batch item (item_id[pk], sib_id[fk], product id[fk], qty, unit cost)
    - stock in batch item attributes (item_id[pfk], attribute id[pfk])
    - stock in batch item barcode (item_id[pfk], barcode id[pfk])

    - stock out batch (sob_id[pk], warehouse id[fk])
    - stock out batch item (item_id[pk], sob_id[fk], product id[fk], qty, unit cost)
    - stock out batch item attributes (item_id[pfk], attribute id[pfk])
    - stock out batch item barcode (item_id[pfk], barcode id[pfk])

    - stock transfer batch (stb_id[pk], from warehouse id[fk], to warehouse id[fk])
    - stock transfer batch item (item_id[pk], stb_id[fk], product id[fk], qty, unit cost)
    - stock transfer batch item attributes (item_id[pfk], attribute id[pfk])
    - stock transfer batch item barcode (item_id[pfk], barcode id[pfk])

Should I have qty field?
Where should I put qty?

I am thinking of using sum() in the stock in and and minus() stock out transaction to get product quantity, but after thinking I am going too complicated and stuck at here.

I also thinking to use qty field, for every transaction it will auto adjust the qty field, but afraid that may be the final qty doesn't tally with the transaction calculation.

Please advise.

Best Answer

Your sum idea works to a point, and then you will end up with a slow transaction whenever you add or remove items from stock due to scalability issues.

The way I did it for a similar problem, was to maintain a running total table, which would work in your case.

I had an audit table as well for accounting purposes, which had original values in case the accountants got twitchy, but the running total option is more scalable.

Related Question