Originally Posted by
Micron
OK, this is not how I like to make in depth suggestions - I put the concepts on paper as do many seasoned developers but this is from my head, and is not based on a crystal clear understanding of the process. It's only meant to support the notion that there is a bit of a lack of understanding db normalization. It is by no means complete (or necessarily accurate). It seems to support what Ajax just wrote about not mixing transactions with your parts table.
These are what I think are the bare minumum of tables and respective fields that you should have for the issue you presented. However, it does not cover the idea of having a BOM which may be what you really need, but one has to know where to stop with the suggestions. A BOM would allow you to get a complete list of parts, sub assemblies and quantities for any build. You could choose a build item and pre-populate a parts list and report on stock availability in one go.
(Desc is short for Description)
The units you manufacture:
tblAssy
AssyId autonumber; primary key (some add PK to the end of the field name)
AssyName
AssyDesc
etc.
ALL of the parts you "consume" on any build. Perhaps even parts that you use as sundry items as well - depends on what the db is to support.
tblParts
PartID autonumber; PK
PartNumber
PartDesc
etc. (supplierID as foreign key from tblSupplier perhaps?)
Parts transactions do not go into parts table
tblInventory
InventoryID autonumber; PK
PartIDfk - PK from tblParts
TransType (transaction type) PK from tblTransactionType?
QTY single field for + or - qty. The form sets -2 or 2 based on transType. Stock count is therefore the sum of all +'s and -'s for a PN
etc.
tblBTO
btoID autonumber PK
If a build has data like start/end/promise dates, customer, etc. then no parts data goes into these records because the BUILD IS ITS OWN ENTITY thus see next table. Probably best to have bto and parts used on it separated regardless. So the bto is an entity and so is/are the part(s). Usually when 2 or more entities are involved, there is a junction table to link their records because in reality you have a many to many relationship (many bto that use many parts).
tblBTOparts - junction table for a build and its parts
btoPartID autonumber PK
btoID - FK from tblBTO (same ID value for every part used on any ONE build). These 1st 2 might need to be a composite key so that the same part cannot be added again.
InvIDfk - part PK from tblInventory NOTE: linking to inventory would permit a direct link between stock count and a bto. If you link to tblParts, you need a work around to know if there is sufficient stock for a build.
btoPartQty - how many of a part? I think to have a BOM table provide a part qty could only work if a bto is for ONE assembly and no more.
A junction table for PartsSuppliers may be required if several vendors supply the same part and it is the same PN in your system.
Add standard stuff not related to this, such as tblCustomer, tblUser and so on.
HTH