Well, maybe.
It may not be appropriate to have the vendor ID in the tools table. For instance, suppose you have two tools which are functionally identical, except for the manufacturer. 24 inch Aluminum Pipe Wrench, for instance. One is made by Rigid, the other by Sears. Do you really want to have two records? What could be done is to have a many-to-many relationship between the tools, and the vendors. (using an intersection table)
So
Tools: ToolID*, ToolDescr, CategoryID
VendorTools: ToolID*, VendorID*, VendorPartNum
Vendor: VendorID*, VendorName/Address/Phone/...
(The * indicates the Primary Key of the table)
I would probably look at the inventory table a bit more, too.
One suggestion would be to add a location table, where the locations could include "Scrap", "Shop", "ToolRoom", or an Employee ID. (if in the shop, the transaction record transaction type would define the reason - repair, sharpen, adjust, etc.) Having an anticipated return date field would also be useful, too. When you check out a tool that you expect back by 2:00 PM, it would be helpful to be able to let the manager know this, if he wants the info for scheduling.
Then, in the inventory table, you would have the quantity at which location.
Suppose you check out a pipe wrench to Joe. You would decrement the Toolbin inventory record for pipewrench by 1, Increment Joe's inventory record for Pipewrench by 1, and update the transaction record with the date that the transaction took place. Then, the pipe wrench gets broken and needs to be repaired. Joe returns the wrench to the toolroom, and a transaction record gets generated which decrements HIS inventory of the wrench, and increments the toolroom record - with a transaction code of Return. Then, you issue a repair transaction which decrements the toolroom inventory count, and increments the shop inventory count, sets the transaction code to Repair. And, since the shop told you that they would have it fixed by next Thursday, you store that day as the expected return date.
One additional point. You might also want to have the ability to keep track of individual tools. Hammers, chisels, and the like, you probably don't need to keep track of individual items. But, when an item is very expensive - a portable welder, or a backhoe, for instance, these tools need to be handled somewhat differently. This is known as serialized tracking, where each item gets its OWN ID (Serial) number. In this case, you would want to include a serialized flag field (Y/N, True/False) in the item table. If a part is marked as serialized, then its inventory quantity can only be zero or one. And, your application needs to verify this for each item during transactions.
On a side note, there's a third 'class' of item inventory management that, while probably wouldn't be needed in your case, often comes up, and that is lot controlled inventory. For lot controlled inventory, where you may not need to track the items individually, but you DO need to track the lot or batch number of the parts, you add another flag field in the item table. This Lot Control flag is used to let inventory control system know that lot control is required, and an additional Lot Number field is needed in the inventory table. And, you could have multiple, otherwise identical records with different lot numbers. The LotNumber field can't be part of the primary key of the table, but, it should be part of a unique key definition.
Lot controlled inventory is very common in the automotive and aerospace industry, as these manufacturers must have a means of tracing those autos or planes which were built with a given lot number of component parts, for recall purposes.
dBforums
>
Inventory and Point of Sales
08-18-03, 17:38
Linear Mode
