I am building a small inventory database for copier supplies at my place of work. In the end this database will be automated to subtract taken from the shelves and add any incoming orders. Right now I am having a hard time with creating the tables. I don't have much experience with SQL except for a class I took a while back. I can't think of tables that I would need for this project. Currently we use a manual system with the following information.
Manufacturer
Model
Part #
Reorder Level
Reorder Quantity
On Hand
Quantity on Order
I don't need to know price or anything like that. I think all of that could go on one or two tables. But as stated earlier, I haven't had any experience with SQL in a while and would like some feedback.
Best Answer
I'd consider 3 tables to describe the items, and a table to describe the transactions against those items. Something like:
The above tables can contain whatever extra columns you need that make sense for each table. Perhaps the manufacturer's phone number, or perhaps where you order parts from, etc.
Here is the table where you keep track of when items were consumed or received:
To show this design, we'll insert some sample data:
This shows how you can see a running total and when you need to reorder:
The output from the above query: