design for 'simple' inventory system

Question

I want to make a relational database system for a local computer hardware non-profit. There are two inter-related features that I want to figure out how to implement. First, we need to manage our inventory of parts, and second, we need to track donations of computer systems as they come into us, and also as we grant them out.

First, we have a simple inventory of parts. We don't need to track individual parts ( we couldn't, anyway ), so what I want to model is the the count. My thought is to have different 'bins' of parts, that have just a simple count. So if we move a video card from its 'inventory' bin to the 'recycle' bin, I want a -1 to video-card-inventory, and +1 to video-card-recycling. Bins may be more well-defined as needed, such as pci-video-cards, agp-video-cards, etc. Or, if we count our inventory, we might need to do a -3 from inventory, and +3 to 'shrinkage'.

The point of that is to know at any time how many, say, video cards we have, how many sticks of ram, etc. The two aspects of a bin would be what kind of part is in it ( at whatever level of specificity, such as 'old-misc-card' or '32MB-3.3v-agp-video' ), and the purpose of the bin, such as 'donation-in', 'inventory', 'recycling', 'store', 'shrinkage', etc.

Also, we would like to see a trending of ebb and flow of parts, and historical data, so we need to do queries of inventory at any time.

So how would I design table(s) to handle that? I'm thinking it would be something like a double-entry ledger. I might have one table called 'BinTransactions' where there would be from_bin, to_bin, and amount. The amount would be a positive integer, and if I want to write a query see how much would be taken out of an inventory, I would make it negative. Something like "SELECT SUM(amount) * -1 FROM BinTransactions WHERE from_bin = 'inventory' AND time_period = ..."?

The second part of this is the computer systems themselves. They come in as donations in whatever state they're in. Parts may be taken out from them and put into inventory or recycling; parts may be taken out from inventory and put into computers. I guess I could make a computer a

A computer finally makes its way out of our system as a grant, but that structure sort of has one level of nesting. It's a collection of computer parts in the computer, but there is also monitor, keyboard, mouse, perhaps speakers. And a large grant may be several systems, also with networking equipment. Should 'nest' the logical hierarchy of groupings ( parts into computers, computers into grants ) , or would it be okay to just have every donation just one big group of parts? If it were one big group of parts, wouldn't necessarily know which parts went with which computer if we got a single computer back from a grant. Also we would like to be able to know from reports "34 complete systems were donated this quarter..."

Answer 1

This sounds like a problem that has already been solved: http://sourceforge.net/projects/phpmyinventory/ http://sourceforge.net/projects/asset-tracker/ These are just two links I found on sourceforge by typing 'inventory' in the search box.

The most basic description is this:

• Computer parts come in.
• Computer parts get stored.
• Computer parts leave.

I am not strong enough with SQL to recommend a way to handle computer donations / sales and their breakdowns into parts - I would probably let some outside application handle most of that logic, and keep track of this:

• Who donated a computer
• To whom was a computer given

As far as logic handling: for instance, a webpage that says: "Woo! We received a computer! Does it have:

1. A power supply?
2. A video card?
3. A sound card?
4. And so on, and so forth."

The same can be done when giving a computer away - but you need to remove '1' from storage for each of the parts that goes away!

So, how do we handle the inventory? Well, you could have one table that looked like this:

video card   | recycle | donation-in | storage | garbage
sound card   | recycle | donation-in | storage | garbage
power supply | recycle | donation-in | storage | garbage

With each bin being how many there are at a point in time. If you want to make it more specific, you can add a "description" column, so you know how many of each type of video card you have, for instance.

And a table that looked like this:

part name | from_bin | to_bin | quantity

Most of the logic for moving the quantities around should probably be handled by the application (I am more of a Ruby-on-Rails guys, so this makes sense to me). Hope that helps.

Answer 2

For the computer is a set of parts bit, the way we track assemblies in LedgerSMB is this:

We have a parts table, in which each part has an assembly flag.

We have an assembly table which provides hierarchical mappings between parts and other parts. Assemblies can be built out of other assemblies, parts, and labor.