[RESOLVED] Database design query

[tropix100]

I have a created an access database which currently has 2 tables.
One table (ItemsTable) contains the fields ItemCode, ItemDesc, Location, OpenBalance, plus some other not so relevant fields.
The other table (TransTable 'as in transaction') contains the fields, Item, QtyIn, QtyOut, and very importantly, Batch, plus some other not so relevant fields.
This operation has to operate on a FIFO (First In, First Out) system.

What I am now asking for asistance with is....

1. Am I on the right track with my approach to stock control with this structure.

2. Should I imcorporate a running balance per Item, per Batch somehow, or should I program calculations to get the current balance per Item, per Batch?

This is my first attempt at a stock control database so I am very much open to suggestions/samples.

I am working with vb.net and displaing data in a dgv via sql statements with oledb.

Sorry if I have not eplained this situation properly.
Thanks for any help.

[si_the_geek]

1. Not too bad, but I would recommend thinking about having 3 tables instead - as presumably the ItemDesc (and probably others) will be used repeatedly.

As a rough idea (based on assumptions of what your fields contain), the tables I would suggest are:

ItemTable: ItemCode, ItemDesc, plus some other not so relevant fields.
BatchTable: BatchCode, ItemCode, Location, OpenBalance
TransTable: BatchCode, QtyChange

Rather than separate fields for QtyIn and QtyOut, QtyChange would contain positive and negative values - which mean much less work to get a total (you can use a simple Sum).


2. A running balance is a bad idea. If there are multiple users, it is hard to be sure they won't update/lock it at the same time. Even with a single user, you can't be sure there won't be an error while updating it.

It is much safer to have separate records which can be used to calculate it as needed - then in the worst case you will only miss out on the quantities that are being edited at that particular moment (which by definition are unreliable anyway).

[tropix100]

Thanx Si.
Your advise has been helpful and changed my way of thinking.