[DB Design] Sometimes-Tracked Stock

[lcthree]

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:

Items
-----
Barcode [PK]
Description
CurrentStock
MinimumStock
 
AssignedStock
-------------
JobNo [PK]
ItemBarcode [PK] [FK]
QuantityAssigned

And here's what it might look like if they all had serial numbers:

Items
-----
Barcode [PK]
Description
MinimumStock
 
Serials
-------
SerialNo [PK]
ItemBarcode [FK]
 
AssignedStock
-------------
ItemSerialNo [PK] [FK]
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:

Items
-----
Barcode [PK]
Description
CurrentStock {this might not match result of querying on Serials/AssignedStock}
MinimumStock
HasSerialNo
 
Serials
-------
SerialNo [PK]
ItemBarcode [FK] {where HasSerialNo = true}
 
AssignedStock
-------------
JobNo [PK]
ItemBarcode [PK] [FK]
ItemSerialNo [FK] {null if HasSerialNo = false}
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.