I am in the planning phase for a database to control inventory. I am currently reading 2 books on DBM. "Concepts of Database Management"(textbook) and "An introduction to Database Systems" Date. I am new to DBMA but I think I am grasping the logic. Okay I hope that is enough background let's get to the question.
I am trying decide on how I should setup my inventory. The problem I am having is my inventory seems different than most. I want to be able to print a Delivery Ticket and it adjust the inventory accordingly.
I have New Inventory, Reconditioned Inventory, TO BE reconditioned Inventory, and Customer Inventory. If I sell an item to a customer, I usually trade in something of theirs from their inventory to my to be reconditioned inventory and give them a credit on it. Where it gets even more difficult is the way items are charged for.
We purchase blank stock from a third party, a pipe. We then have it threaded with a certain thread size and then install equipment to it.
Each pipe can be a different material(metal or composites), a different size, different threads, different coating(internal or external painting).
So with this information would I am trying to decide how to treat each part in the database.
Should I have fields for each section of the part Size, Thread1, Thread2, Material, coating, billing code, location, price;
Should I use the standard table data:
Pipe (Partnum, Desc, OnHand, etc.)
I want to reiterate that when we first inventory the equipment it is blank, no options. We put threads on it, coat it, etc and each part adds cost to the total equipment.
Although it would be nice to get a solution handed to me on a platter, I am looking to learn. So if you don't want to just give me an answer at least ask questions that you think will lead me to my answer. I hope that makes sense.
IMHO, I would have a parts table, containing 'standard' data. Part Number, Revision, Description, Specification, etc.
Have a Locations table, containing the possible inventory storage locations.
Keep the inventory in a table containing the Part Number ID, the Location ID, and the quantity and units on hand, at that location.
I would probably also have a process table, containing data specific to the processes that are being applied to your material. Threading. Cutting. Finishing. Etc. You could probably keep some standard times in this table as well.
Then, when you process a part, you store a transaction record pertaining to the process you have done. The datetime, the source PN , Quantity (a negative number), location and unit of measure, the destination PN, quantity (positive number) , location and unit of measure, and the process used. Possibly a duration, or hours required to perform the task.)
When you add this record, you also reduce the quantity at the 'From' inventorylocation record, and you increase the quantity at the 'TO' Inventorylocation record.
This lets you change units when you change the part number. For instance, suppose your raw material pipe is stored as the number of 20 foot sticks, and you are cutting it into 1 foot pieces. When you remove 3 sticks, you should end up with 60 1-foot pieces. If you were only needing 50 pieces, you would return a short stick to the source location, with an override unit of feet, and a quantity of 10.
Whenever you perform a physical inventory, you may need to adjust the quantity on hand at a location. Your transaction would reflect this, with a negative quantity added at the location, and a positive quantity at a 'missing' location, and the appropriate transaction type. Likewise, when you receive material, it has a transaction record as well, with a from location of a PO, say, and a transaction record indicating that it's newly received. When you ship a part out, it's transaction record has a TO location of a customer ID or similar. Maybe a sales order reference. If your received parts must be inspected before they can be used, you would have quality inspection transactions - the parts go from receiving into a quarantine location, then, after inspection, they are either accepted, and have a new transaction record showing them moved from the quarantine location to the normal inventory location, or, if they're rejected, they get 'moved' to a ReturnToVendor holding location, or a 'Rework' holding location.
Last edited by loquin; 10-02-09 at 16:10.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert
Thank you for the response. I think my error was trying to jumble too much information into one table. I understand the process much better. It makes a lot of sense.
Right now everything you talked about I am doing by hand. When i receive material I enter it into a ledger(hand written). When I send product to be coated or threaded, I issue a material transfer and then enter it into the ledger. Counting inventory sucks lol. Especially when your products weigh in excess of 200lbs a piece and you need to rotate it to count it. I think with a database it would be more accurate and easier to count than a ledger.
I do eventually want to incorporate sales and service into the database but I am starting with inventory.
Thanks again for the response. I'll let you know what I come up with.