Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Thank you, great idea. I will follow your method. but I still have little bit problem with the qty. product, barcode, qty. 1 product have 0 or many barcode 1 barcode belongs to 1 product 1 product have 0 or many qty 1 barcode have 1 or many qty Some product qty might be depends on qty of barcode Some product qty might not have barcode –  cww Apr 27 '12 at 5:59
    
Please ignore the above explanation. 1 product have 0 or many barcode 1 barcode belongs to 1 product 1 product have 0 or many qty 1 barcode have 1 or many qty Some product qty might be depends on qty of barcode. Some product which doesn't have barcode, so qty is depends on number of goods left. Hope I didn't sound complicated. –  cww Apr 27 '12 at 6:05
1  
Just have cross-reference tables that resolve your many to many relationships. You don't need to worry too much about number of tables, more about width of table rows. –  user1240 Apr 27 '12 at 18:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.