Inventory Tables Design

[Guest]

I’d like to create a database that will, among other things, track the location of inventory. So far I have three tables as follows:

Table 1 – Location
LocationID (Primary Key)
Location (Lookup Field of Warehouse; Regional Office; District; or Employee)

Table 2 – Inventory
InventoryID (Primary Key)
LocationID
CategoryID
Description
Make
Model
, etc.

Table 3 – Categories
CategoryID (Primary Key)
CategoryName (Vehicle, Uniform, Office Equipment, Tools)

My question is with table 1. If an item in inventory is assigned to an employee, I need to track which employee; if an item is assigned to a Regional Office, I need to track which RO; if an item is assigned to a District, I need to track the District; and if an item is assigned to the warehouse – well, there is only one warehouse. Do I need to create a table each for employee, regional office, and district? How do I relate them back to the Location table?

 

[Jeff Boyce]

Katherine

You appear to be using your Location table as a lookup table, to categorize
what kind of location, and your Category table as a lookup of types of
inventory items.

I'm concerned about your "Inventory" table, however. It seems like you're
missing a table or so. By not having a separate "signed out" table, you are
forcing details about an inventory item and the association of that item
with a category and location all together into one.

Here's another approach -- first, keep your two lookup tables (tlkpLocation,
tlkpItemCategory--I renamed it). Then, consider:

tblOwner
OwnerID (Autonumber, primary key)
LocationID (foreign key from your Location lookup table) -- this is
really an owner "type"
OwnerName
... (other info about "owners", like phone number, physical address,
....)

tblInventoryItem
InventoryItemID
ItemCategoryID
(attributes of this Inventory item, like your Make, Model,
SerialNumber, ...)

trelSignedOut
SignedOutID
InventoryID (foreign key from tblInventoryItem)
OwnerID (foreign key from ...)
DateOut
DateIn

I am still bothered by this design, as it leaves out some of the complexity
in the real world. But if you only need to know the "name" of the entity to
which an item of inventory is checked out, then the "Owner" table should do
it. The reason I added the DateOut and DateIn was to provide a historical
listing of who had what when.

Hopefully, there's something in this that makes sense ... I'm feeling a
little holidazed <g>.

Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

First of all I want to thank you for your reply - it is
very much appreciated! I'm trying to rethink the design
of my inventory database to allow the tracking of various
transactions. For example, the annual inspection of
certain inventory items. Would you be willing to look at
my design again? I'm a little slow at this so it may be a
day or two. I have created several simple databases that
are being used by my agency, but this one is quite
complicated and has left me confused.