Table Design Input

[tmyers]

I am working on building a program to help my work deal with wire cuts but am curious how I should make my tables for it in regards to the wire cuts themselves.

Currently I have two tables, tblCutLog and tblParallelCut. tblCutLog handles the single item inputs such as a single size of wire that is a particular color, such as 12 gauge green and X amount of feet whereas tblParallelCut does multiple sizes and multiple colors such as 3 cuts of 8 gauge that are brown, yellow and white plus 1 cut of 6 gauge green.

Should I keep the tables separate or is there a way to combine them into one? I am worried that I am overlooking something that can allow this to be a single table.

 

[Ranman256]

i actually do wire db.
I have a tWire table, holds the WireID, gauge, color, etc.

a tCut table holds the compound wire result:
CutID, Wire1, wire2, wire3, wire4, CutDate, etc

the parallel cut will fill 1 thru3 or sometimes 4.
these fields are filled with the key from tWire table.

 

[tmyers]

I think you may become my favorite person Ranman since you know exactly what I am trying to do .
Currently I have a tblProducts which holds a master list of all the various catalog numbers of wire we cut. Below is a snip of my current layout that I am still playing with.

 

[tmyers]

Ranman (and others of course!), I have another question regarding this same project's tables I am hoping you could help on.

I have progressed to the point of managing inventory, not in the grand sense but on a minor scale and want to make sure I am doing this right. Here is a snip of the structure:

The pulling head side of things I feel is pretty cut and dry. Users will select which pulling head they want and how many they want and then there will be a query to update tblPullingHeadInv's [QtyAvail] accordingly. The main problem I am having involves the reel inventory. I have to be able to track these reels as they are thousands of dollars a piece so we need to know when and where they go when used and when they are back and available again.

Would it be recommended to handle this differently that the direction I am currently heading in? I don't really have experience in dealing with history tables and such so I am just shooting in the dark on how I think it would be structured. Thanks in advance!

 

[RogerCooper]

You need a transaction table that records everything going in and out of inventory. Then you have a full audit trail. Transaction table would have autonumber key and reference the information you are tracking.

The inventory in a location should be the sum of all the transactions, even if you try to keep up to date in a separate location.

 

[tmyers]

It took me all night thinking about it, but I was able to figure this out as well
I had it all messed up and redesigned the tables and now its all working. Many to Many join tables are still a rough thing for me.