Inventory database

Databases
1

I have a charity that collects used sports gear for needy kids. I need to track the type and amount of gear we receive. Here’s the question- How can I track the quantity of a specific item AND where that item came from.

Example:
5 jerseys received from store A
7 jerseys received from store B
20 jerseys received from store C

I need to be able to say we received 32 jerseys total. I also need to break it down by source… store A has donated 5 jerseys.

I will use MySQL and phpMyAdmin. Any help would be appreciated.

Thanks

2

I would start by setting up a table to collect the inventory itself. This would include columns such as an ID field, name, type, attributes (size, color), etc. From there you would also need to setup a table to collect the donors, in this case stores. This would be most like an address book with an ID field, name, address, phone number, etc. You may want to duplicate your donor table to catalog the kids or their organizations that you’re donating the jerseys too, if you’re interested in collecting that data. Finally, create a transaction table where you store any donations to the charity as well as donations to the kids. So that table might have an ID field, donor ID, date, number of jerseys, etc.

This way your inventory is calculated based on the sum of the transactions. You query all transaction records from store A and sum the total donated. Likewise, you could query all stores and receive the sum of all transactions between certain dates.

3

Thanks for the reply. I follow the Donor table and Transaction table. Not sure about the Inventory table. Would I need an Inventory table or could that just be generated by a query of the Transaction table?

4

Well you could store a description of the inventory donated in the transaction table. Maybe a quantity field and a description field. Keep in mind that you will still be able to sum the number of donated items by donor, but you won’t be able to specify which items. So for example, if donor A gave you 10 helmets and 10 jerseys, you would know that they gave you 20 items, but specifically which ones you would need to manually read the descriptions to figure out.

Using an inventory table to catalog the items will benefit you down the road with reporting but it’s certainly not required to accomplish what you’re asking for.