I've found several examples of an inventory database. But I'm looking for someting a little bit different.
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.
What's got you confused? I don't necessarily think you need a trash table or resharpen table. Maybe a "return status" type field on the inventory table? it appears you can have only one action that can occur for a piece being returned - returned, trashed, resharpen, etc. so one additional field on your base table would probably be ideal.
Hmmmm.... inventory table. So do I still put the Qty in the Tool Table or do I move it to the inventory table? I haven't had a chance to dig into it at all yet today. I'm probably asking dumb questions, but I feel like a fish out of water today.
I'm using my bosses computer because IT decided to try 64-bit out on me and they never finished the installs and they are not here today and my computer is not finished. AHHHAAHAHAHA!!! I feel like someone is stepping on my oxygen supply.
If you had qty on the tool table, you could derive the "on-hand" number based on the inventory table. qty - (trashed + resharpen), assuming once it resharpened, it's status is changed to "returned".....
If each employee has his own unique ID and each tool, not each category, has its own unique ID the "Checked Out" table could function a little differently. You wouldnt need "CheckedOutType" as you key would be emp ID and tool ID. Then maybe a checked out status - returned, trashed, resharpen or whatever other status you could possible have (probably add a status table with an ID and use that on Checked Out). If returned, it's on-hand, other status would indicate it's not in inventory for one reason or another. Make sense?
Yes that makes sense and I was thinking of that today. The checkedOut table was a table to record employees checking out tools.
I think it would be better to understand it for myself if I renamed the table Transaction table. Then I could have transaction types. (check out, check out forever, check in to inventory, check in to trash, check in to resharpen, etc.)
I'm not to savvy as to how to handle the inventory increments though. I guess that will be a different question. I'll be on vacation this weekend so I'll post what I come up with when I get back or maybe sooner.