Connecting Tech Pros Worldwide Forums | Help | Site Map

[DB Design] Sometimes-Tracked Stock

Newbie
 
Join Date: Oct 2008
Posts: 6
#1: Oct 27 '08
Hi, my question is about database design. I'm trying to design a database for a stock control program as part of a larger system. The problem I am having is that some items in the database have individual serial codes, and others don't (all items have a barcode to indicate which item is it).

I've simplified the details here, but here's what the item table might look like if no items had individual serial numbers:

Expand|Select|Wrap|Line Numbers
  1. Items
  2. -----
  3. Barcode [PK]
  4. Description
  5. CurrentStock
  6. MinimumStock
  7.  
  8. AssignedStock
  9. -------------
  10. JobNo [PK]
  11. ItemBarcode [PK] [FK]
  12. QuantityAssigned
And here's what it might look like if they all had serial numbers:

Expand|Select|Wrap|Line Numbers
  1. Items
  2. -----
  3. Barcode [PK]
  4. Description
  5. MinimumStock
  6.  
  7. Serials
  8. -------
  9. SerialNo [PK]
  10. ItemBarcode [FK]
  11.  
  12. AssignedStock
  13. -------------
  14. ItemSerialNo [PK] [FK]
  15. JobNo
Notice absense of CurrentStock in Items, as this could be computed, also, quantity of a particular item assigned to a particular job could also be computed

My problem is that while some items are individually tracked with serial numbers others are not, just a note of quanities is used. I can't figure out a comprimise between the designs which allows for this.

Here is a design I'm thinking of:

Expand|Select|Wrap|Line Numbers
  1. Items
  2. -----
  3. Barcode [PK]
  4. Description
  5. CurrentStock {this might not match result of querying on Serials/AssignedStock}
  6. MinimumStock
  7. HasSerialNo
  8.  
  9. Serials
  10. -------
  11. SerialNo [PK]
  12. ItemBarcode [FK] {where HasSerialNo = true}
  13.  
  14. AssignedStock
  15. -------------
  16. JobNo [PK]
  17. ItemBarcode [PK] [FK]
  18. ItemSerialNo [FK] {null if HasSerialNo = false}
  19. QuantityAssigned {1 if HasSerialNo = true}
Unfortunatly this has several dangers to data integrity and consistancy. Can someone please point me in the right direction with this design? Thanks.

Reply