1

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..."

user151841
  • 17,377
  • 29
  • 109
  • 171
  • Is this going to be updated every time a sale is made? Or maybe it's only updated whenever inventory is taken? – wallyk Jan 08 '10 at 04:09
  • 3
    Although I usually say the opposite, I think this is actually a case where a question *shouldn't* be community wiki. Clearly some answers are going to be smarter solutions than others, objectively speaking. – John Feminella Jan 08 '10 at 04:13
  • Okay, I may grant that there is an objectively better solution, but I don't think I am capable of determining that! :) I think there will be different proposals with different benefits and drawbacks. – user151841 Jan 08 '10 at 04:21
  • @wallyk -- the 'store' has no inventory or stock of its own. People come in and buy things out of our inventory. So, when someone buys something, we would need to know "taken x things from 'inventory' to 'sales'" – user151841 Jan 08 '10 at 04:25
  • @unknown RE: community-wiki: That's the point of up votes on this site. While you may not be able to determine the "best" answer. The community can indeed vote to help you with that information. – Kevin Peno Jan 11 '10 at 21:55

2 Answers2

1

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.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
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.

Trevoke
  • 4,115
  • 1
  • 27
  • 48
  • I was hoping to find a solution where a computer was a set of parts, more like a bin. It will have transactions, like a bin, where parts go in and out. – user151841 Jan 20 '10 at 14:36
  • The reason I want to stay away form 'hard-coding' part statuses in columns is because a computer that we get might be totally broken into parts and recycled. The entities I want to track in this model are the movements of parts -- the transactions between bins and computers -- not systems themselves. – user151841 Jan 20 '10 at 14:56
  • Likewise, a system may come into existence by being assembled completely from parts. So the model I'm wanting to make is a system as a conflux of parts, not as an entity itself. – user151841 Jan 20 '10 at 15:08
  • Add one more table: id | part_name Then you can have : 1 | video card 2 | sound card 3 | power supply 4 | keyboard 5 | mouse And you can just add rows to every other table by linking to those names - now the names aren't hard coded. As far as hard-coding the statuses, same thing. Make a table of statuses, and then link to those. That way you can end up with a table that does this: date | part_id | from_id | to_id 2010-01-20 14:15:14 | 5 | 1 | 4 – Trevoke Jan 20 '10 at 15:17
  • You want "many parts" to become "one computer" -- and I don't know if you want that computer stored in the database as an addition or just shipped out. I'm not sure a database can do this for you, but with front-end logic, a database can be told "Okay, now we're removing one of each of those items, and adding one of this item". A database holds data and relationships between data. It doesn't hold business logic :) – Trevoke Jan 20 '10 at 15:20
  • Well, okay, but in this project, the main goal is to keep track of the flow of parts. If the final resting point of a part is in a system instead of a bin, and the database can't model the granting out of a system, that's fine. What we want to track in house is our inventory, which comes from part flows. – user151841 Jan 20 '10 at 15:47
  • Let me follow your train of thought. Let me know if I'm wrong. You want to track which parts go from where to where. In order to do that, we need to know a part, an origin and a destination, a quantity, and maybe a timestamp. A part is... A part. You can put it in a table with an id and a description. An origin and a destination are locations: out, in, recycle, fridge. Those can be put in one table with an id and a description. The quantity, well, depends on the transaction. That has to stay in this table. The timestamp just gets tacked on to it. (continues) – Trevoke Jan 20 '10 at 16:25
  • Now, I just described exactly the system I suggested to you, minus the original table which says "we have this many of that part in yonder location". Presumably because doing the original inventory would be a pain and you want to avoid that. If all you -really- care about is the flow of parts through your location, then you don't need that last table. As a warning though, running reports might be really painful. Is that pretty much your thoughts? – Trevoke Jan 20 '10 at 16:27
  • Well, we don't really want to track parts individually. The system I was envisioning was more like a double-entry accounting system. In that database structure, I have a transactions table, whch says my checking account is -500, and my credit card account is +500, and the respective balances at a point in time are x and y. I don't really need to track each individual dollar that went from one account to the other -- I just care *how much*. – user151841 Jan 20 '10 at 21:24
  • Similarly, in our shop right now, we don't track parts. All we have are inventory counts ( "We have 50 hard drives" "We pulled 10 sticks from RAM from computers" ). In this way, parts of the same type are fungible, like dollars. All we want to track in this inventory system is transactions, the amounts to and from, and use that to build balance queries, such as "You have 50 hard drives right now" or "Inventory of hard drives have been trending downward since June". Just like currency accounting. – user151841 Jan 20 '10 at 21:27
  • Even if we wanted to track parts, we couldn't organizationally ( who's going to type part serial numbers into the database? We can't afford thermally-printed serial number stickers -- this is a charity ), nor does it really gain us anything. Who cares that RAM stick #283985209348 was pulled from a computer and put in a bin? We just threw one in the bin after it passed testing. +1 to inventory, -1 from testing. – user151841 Jan 20 '10 at 21:31
  • Ah! I see. What I have been describing to you does not track single parts, only amounts. I still believe that it is the simplest way to track all the data you want to track. I would add a simple SQL job to regularly do an inventory query and store that in another table with the date and the bins, so you can see, over time, the changes in balance in each bin. I think the misunderstanding stems from what we think 'inventory' means. I just mean "keeping track of what we have". I tell you what - this will be a good exercise for me, I'll build you something simple to show what I mean. – Trevoke Jan 21 '10 at 13:43
  • :) OK -- what database? If it's not postgres or mysql, I won't be able to run it. – user151841 Jan 21 '10 at 14:18
  • MySQL.. It's what I have installed. It'll also be Ruby on Rails. It'll take me a little while to do, since I have other obligations - so if you have more questions, about exactly what I am suggesting and how it differs from what you want, by all means, ask! I am really hoping I get your firstborn child, or at least a green mark, when this is all set :p – Trevoke Jan 21 '10 at 14:37
  • hm... I don't have a RoR environment! Is this just the table structure and queries you're writing? – user151841 Jan 21 '10 at 15:21
  • Sure. I'll just give you the database skeleton/template, then. – Trevoke Jan 21 '10 at 20:19
  • Here it is : http://gist.github.com/283897 What is missing is a stored procedure / scheduled task to do a regular poll of the bins and add the total to the table 'monthly counts' - for easier reporting purposes of the changes in quantity over time. Let me know how that goes for you. – Trevoke Jan 22 '10 at 16:42
  • Does that link help you at all? Anything amiss? – Trevoke Jan 25 '10 at 20:22
  • Sorry it took me a while to respond, I got busy with other things. In our warehouse, I don't think we'll be able to track parts by type individually. Anything residing in the same box is the same 'part' -- such as 'useable hard drives' or 'hard drives for testing'. So I would get rid of the parts table. There are just things moving from bin to bin, and what they are depends solely on which bin they reside in. Also, I was trying to think of how computer systems cold be like bins, which also accept part flows. If you post the code to another answer, I'll upvote and accept it. – user151841 Jan 30 '10 at 19:48
  • A "hard drive" is a part. Why would you get rid of that, since it's precisely what you're trying to track? "Usable" would be a bin. "To_test" would be another bin. Here... Read this: http://rdbms.ca/database/introduction.html Maybe it'll help you understand better. – Trevoke Jan 31 '10 at 21:52
  • We're not necessarily trying to track that. We just trying to count how many things we have where. The parts table is over-engineering. If we move 12 sticks of RAM and 20 old modem cards into 'recycling', we don't care how many ram sticks we have in there, nor how many modems. All we care about is 32 things went from two bins into one. – user151841 Feb 01 '10 at 18:31
  • Or, your could say in other words, parts get their identity from where they're located; it's not inherent. Or, even another way to think of it is, we really aren't tracking parts. We just want to track the counts of bins at various times. – user151841 Feb 01 '10 at 18:44
  • Let's try a contrived example, then. You have: A "just received" bin with 15 sticks of RAM and 4 mice. It has 19 items. A "tested-good" bin with 3 keyboards and 6 hard drives. It has 9 items. A "tested-bad" bin with 4 power supplies and 2 AAA batteries. It has 6 items. All you want to know is that 4 items went from "just received" to "tested-good" -- and it doesn't matter to you whether the 4 mice went, or one mouse and three sticks of RAM. Is that right? – Trevoke Feb 01 '10 at 19:13
  • Well, they would go into `tested-good-mice-trackball`, `tested-good-mice-optical`, `tested-good-512MB-DDR2`, etc. I mean, those are the boxes we literally have right now. So I'm trying to have a data model that conforms to what we're already using. Otherwise, that makes acceptance very difficult. And I'm not sure that we could even handle a more fine-grained system, organizationally. All parts within their container are fungible. If they are different, they go into another box. – user151841 Feb 01 '10 at 19:28
  • How exactly is that different from the system I've given you? You have parts and bins. That means that if an OPTICAL_MOUSE has part_id 1, and if TESTED-GOOD bin is bin_id 1, then you can have 15 of part 1 in bin 1. And that's 15 good optical mice... In the good optical mice bin. If TESTED-BAD is bin_id 2, then if you have 5 of part 1 in bin 2, then you have 5 bad optical mice. You are welcome to add an "unidentified" part, and only have 587 unidentified objects in your inbox - and then, decrease unidentified in inbox and increase something else somewhere else. Does this still not make sense? – Trevoke Feb 01 '10 at 19:50
  • I'm mulling over how it may be different, but in the meantime, this question that occurs to me: if it's not different, why bother? If it's functionally the same, but structurally simpler, shouldn't I go with the simpler data model? The schema with `parts` is more complicated -- it has an extra table. If we can get rid of it, why not? It would make querying simpler. – user151841 Feb 01 '10 at 20:01
  • Because with the added table, you get much more flexibility, and less data repetition, which means a smaller database. – Trevoke Feb 01 '10 at 20:24
  • Can you give me some scenarios that exemplify the additional flexibility, and the lack of repetition? – user151841 Feb 02 '10 at 15:02
  • *sigh* You don't really have any kind of database experience, do you? If you only have a bin table, then you need a row for "video-cards-good", a row for "video-cards-bad", a row for "video-cards-to-test", and then just one column for each count. And every time you have a new part, you have to add X rows, where X is the number of bins. If you have a bin table and a parts table, then you can have a single row for video cards, a column for each bin, and the count in the appropriate cell. Just add a row for a new part, a column for a new bin - and EVERYTHING gets the new bin. – Trevoke Feb 02 '10 at 18:43