Inventory Database Design

Question

I've found several examples of an inventory database. But I'm looking for someting a little bit different. I'm working with SQL.

I need to keep track of tooling. Employees can check out tooling and the inventory for that tool will be reduced and a that transaction will be recorded in a (checked_out) table. Easy to far.

When the employee returns the tool or tools the employee has a choice. He can either return the tool to inventory. Still fairly easy. Or he could bring the tool back broken and throw it away, in other words record it to the trash table. Or he could put the tool in the resharpen bin and record it to the resharpen table. This is where I get confused.

Answer 1

Sounds like you need a status on the inventory table. For example, a record for an arbitrary tool can have a status field that is a foreign key in the "Statuses" table. The Statuses table could look like this:

ID     Description
-----------------------
1      Available
2      In Use
3      Broken

... etc.

Your Inventory table could look like this:

id     status_id     description
-------------------------------------
1       2            Hammer
2       1            Hammer
3       3            Hammer
4       1            Saw
5       3            Saw

Then, when you calculate inventory, only show records where a tool has a status of "1" for Available. You could also run queries for management to show how many tools are "Broken". Plan for eventually deleting these records once the broken tools are accounted for properly, or, keep them for historical data purposes.

At all costs, avoid creating separate tables for the disposition of any entity in the database. Use a "flag" field (i.e. status_id), so you can add new statuses to an application without needed to infinitely add new tables.

Answer 2

Your setup is wrong, you should not have a separate table for trash, checked-out, and resharpen-bin. These are merely states that the tool can be found.

What you want is a database that includes these 2 tables that will house the data proposed by HardCode in his solution:

Inventory
-----
id (PK)
description
status_id (FK -> Statuses)

Statuses
----------
id (PK)

Answer 3

I like HardCode's schema, but it might be a closer match to the situation being modeled to store a location instead of a status (in any case I'd discourage the use of the word "status" which is so generic as to be meangingless). Location is really what is being tracked here, and could even be used to indicate who is the current holder of the item when it is checked out. I think that physical condition ought to be held separately, if it is to be tracked at all. That would allow finer grades of condition based on tool user feedback or staff observation.

(Responding to comment 2)

I'd expect that you'd have a table to record the individual movements of tools (from location A to location B at a certain time by person Z) and another to record a higher level summary or current location. Much would depend on whether tools were individually identified by serial number for example, in which case you'd track them individually, or whether they were just one of a number of the same tool, not identified individually. This would determine whether you'd record 5 transactions (one for each tool) or 1 transaction of 5 tools. You might just summarise as "5 tools checked out" or you might state "these individual tools are currently at these locations".

Answer 4

This question is answered. I've come along way in this past year. I can't believe I struggled with this at all.