Inventory Management - Warehouse locations

[jharper]

I am currently designing an Access database to manage the stock control process for a small business (4 employees) involving barcoding.

I have done this sort of thing in the past - 8 years ago - so I have a decent idea of what I am doing but I can't get my head around the best way to setup the warehouse locations in Access.

The physical format is xx-xx-xx-xx-xx which represents the warehouse, aisle, bay, shelf & bin e.g.
A-01-03-04-10 which would be warehouse A, aisle 1, bay 3, shelf 4 & bin 10.

I plan on having a table for the warehouses and a second for locations as follows

tblWarehouse
WHID (PK)


WarehouseCode
WarehouseDescription

tblLocations
LOCID (PK)
WHID (FK)
AisleCode
BayCode
ShelfCode
BinCode

This dosn't seem quite right in terms of normalisation though?

With regards to barcoding these locations I am guessing I could just barcode the LOCID with the description under the barcode being made up of the other fields in tblLocations?

Any thoughts or ideas?

Cheers, James

[ranman256]

Sounds good , but note:
As is, you can have repeat items in the same
WHID (FK)
AisleCode
BayCode
ShelfCode
BinCode

If that is true then good, but if you do NOT want 2 different items stored in the same
WHID (FK)
AisleCode
BayCode
ShelfCode
BinCode

then why not key on ALL these fields? That way you CANT put HUB CAPS and INNER TUBES in the same bin: wh1,aisle1,bay2,shelf2,bin3
Keying all fields would prevent 2 items put in the same bin.

BUT if 1 bin CAN have > 1 item, then dont key them.