views:

705

answers:

4

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.

+6  A: 

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.

HardCode
lol - Excellent idea!
John Rasch
Great minds ... ;-)
HardCode
Yeah you covered the data and I covered the structure, I think I'll edit mine to match your status_id
John Rasch
If there is lots of Hammers, and Lots of Saws, etc, you might probably want to have a tool_type table, and you inventory table becomes id, status_id, tool_type_id
Matthew Watson
Oh, of course. I intended to introduce the concept, not complete the database design ;)
HardCode
Yes very good response. I'm just wondering about this a little bit. If I had 100 hammers I would have to have 100 rows in the Inventory table with ToolType of Hammer?
Jesse Seger
Yes. Each row in the inventory table would represent one physical item (or toolset/kit) to "check out" of inventory. Otherwise, you'd have to devise a scheme where you maintain counts of like items. That could be messy, though.
HardCode
+3  A: 

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)
John Rasch
+2  A: 

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".

David Aldridge
I agree: Location could hold 'Store', 'Repair Shop', 'In Use', 'Unknown'. Condition could hold 'Usable', 'Awaiting Repair', 'Destroyed'. Location = Store and Condition = Usable means it can be borrowed. In this case it's 6/half-dozen but in others the granularity can aid selection.
Alistair Knock
I'm learing a lot here. But I'm still confused about the quatity.Let's say I have 100 ToolA's. I check out 5. This transaction is recorded. In the transaction table it says EmployeeID 1 took 5 ToolA's to Machine 4.Where is the quatity of ToolA located in the first place?
Jesse Seger
A: 

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

Jesse Seger