Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

From what I can see, the database design is similar.

One is the consumables that are resident in a warehouse (inventory). The others are items, capital, that are assigned to people or places, but are not consumable (assets).

For database design, does that sound like the right track to start down?

share|improve this question
Is this homework? Also, what is the deliverable here? What are you actually hoping to accomplish? – swasheck Aug 31 '12 at 15:33
In my mind, both styles of database are very similar. However, I'd like to see a more detailed question - this seems to vague? – Max Vernon Aug 31 '12 at 15:34
do you mean, I need to design a inventory system but I found a good asset tracking design, can I apply? :-) – Edward Dortland Aug 31 '12 at 15:58
I would think you might be able to apply a well designed asset tracking system design to your requirements for an inventory system. I would suggest carefully looking at the design you found to see if it fits your requirements. If you have any specific questions, ask them here. – Max Vernon Aug 31 '12 at 16:02
@swasheck No! It's not homework. It's for my job. I'm hoping to accomplish both things in the question. – johnny Aug 31 '12 at 16:26
up vote 3 down vote accepted

LedgerSMB, which is my main project, does both. I don't think we will merge these and the explanation why may give you an answer.

The difference you have to understand is that if you are trying to do both, you are probably trying to do accounting on both. The accounting issues are different and require that different things are tracked. There is room to centralize some information, but others can't be handled as well.

Let's look at the flow items through both systems.

You buy 10 widgets for inventory for $10 each. What you have to track depends on how you value your inventory. If this is FIFO or LIFO, you are going to have to track the financial aspect of the transaction ($100 debit in inventory, $100 credit in payables), and for average cost, you will have to track also the average cost per item. As an item gets sold then you have to record revenue and receivables but you also move inventory value to your cost of goods sold account. Modelling this process really defines a usable inventory system.

An asset control system is different. Suppose you buy 100 desks at $50 each. You then use these for a minimum of five years and depreciate using some method (many methods are acceptable) over that time. This example will use straight-line. To do this properly you also have to track date in service, estimated salvage value (in this case $0 if we intend to donate on disposal), etc. Then each year we will have a transaction which partially moves that $5000 into expenses ($1000 per year using straight-line). After that the asset will be fully depreciated but still in service and we will have no further transactions for the part until disposal. When we dispose even if for $0 on a fully depreciated item, we will still have another financial transaction accounting for this.

I know in LedgerSMB, our asset management side is more complex than our inventory management side.

So your design has a lot less to do with the nature of what is being stored and a lot more to do with the business processes you have to support, the accounting requirements, and so forth. These mean you have to track different information and expect different flows of stuff through your system.

Hope this helps. One thing I would highly recommend is that you do one of two things, however. Either hire a consultant (me! no conflict of interest there ;-) ) or pick up a book on accounting. If you want to support accounting workflows you want to know the basics enough you can have an intelligent conversation with the folks from finance. Domain knowledge here really is important because this is an area that looks really weird to the uninitiated. You don't have to know everything but you should be able to flag issues to talk about with accountants. I got into this through a book I picked up for $2 at a garage sale. It's a vast field though and you want to know what human problems you are solving and what business processes are accepted.

share|improve this answer
So you system does both? – johnny Sep 3 '12 at 15:34
Yes. And it's open source (GPL v2 or later). – Chris Travers Sep 4 '12 at 0:37

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.