Design for an Asset Management System

If this were my design decision, I would probably go with more of an 'Option C' (modified option a).

First, why not 'Option B':

For one thing, I like the clarity that each product has it's own table affords. If it's all one big table with a field to determine the type, the relation isn't as clear.

For another, the indexing strategy would always require that type field to be listed. Since it's only 4 types, the index cardinality is extremely low (SELECT * FROM product_table WHERE type='X' is basically doing a full table scan anyway)

Option C

• Create a parent table that holds only the columns that all types share
• Create each product type as it's own table with their individual columns, with one extra: A link to the parent table
• Create each 'link' table: Product_Option, Model_option, etc with links to the respective keys.
• For those with reciprocal links (MODEL_OPTION, OPTION_MODEL) go ahead and create those tables as well. This will add clarity in your joins for anyone looking at it.

The downside is the complexity of making sure to avoid orphans when things are updated/deleted, and initially designing the queries that use these tables.

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.