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.