Help and advise on simple stock in and out database (1 Viewer)

hullstorage

Registered User.
Local time
Today, 15:26
Joined
Jul 18, 2007
Messages
213
Hi all,

I am creating a simple database to keep records of stock movements.

We have parts that come in and out of our warehouse all which will be scanned at time of movement.

I am thing I will need a few tables and relationships for this and this is the main advise that I will be moving.

Here is how the system works note that there will be no product details and every thing will simply be numbers as follows:

THIS IS FOR ALL ITEMS COMING INTO
[GOODSINWARD]
DATEIN
TIMEIN
SCANNEDBYEMPLOYEE
PARTNOID
SERIALNOID
UNIQUENOID
TOLOCATIONID
ANYCOMMENTS

[GOODSOUT]
DATEOUT
TIMEOUT
SCANNEDBYEMPLOYEE
PARTNOID
SERIALNOID
UNIQUENOID
FROMLOCATIONID
ANYCOMMENTS

[STOCKLOCATIONS]
STOCKLOCATIONID EXAMPLE RACKING NO A001, C003 ETC

ALL THIS IS SIMPLY TO DO IS SO THAT ONCE PARCELS HAVE BEEN PICKED THEN DETAILS WILL BE PRODUCED ON A REPORT WHEN REQUIRED

EVERY PARCEL HAS 3 BARCODES AS ABOVE AND THE LOCATION WILL ALSO HAVE A BARCODE SO EVERYHTING WILL BE INPUTTED MAINLY BY SCANNER.

THANKS FOR ANY ADVISE YOU CAN GIVE
 

Minty

AWF VIP
Local time
Today, 15:26
Joined
Jul 26, 2013
Messages
10,354
In very simple terms I would suggest putting your product details into one table - with a dateRcvd and dateShipped field, and a Unique Record ID.
Depending on your business needs you could then record the current location into a separate Movements table with a simple Record ID, date and current location field.
Once the product ships you complete the shipped date and you have a complete record of in, out and everywhere it went internally.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:26
Joined
Sep 12, 2006
Messages
15,613
given your goodsinward/goodsoutward table, I note you have no qty field.

does that mean everything is unique? if so then book it in with goods inward. The goods outward just needs to record some relevant details about the movement out.

anything without goods out data should be in stock

Alternatively, maybe you need a quantity field! I would still only have one table, as was already suggested.
 

Users who are viewing this thread

Top Bottom