Hey, guys. Yea, yea, I know it's Friday and all...sorry to ask you to think today...

Here's the scenario.

Inventory/purchasing system with 4 types of items:

1. Non-inventory items (items known, but not stocked)
2. Bulk items (like paper towels, nuts and bolts, etc)
3. "Regular" items (like oil filters, etc...non bulk items)
4. Serialized items (capital assets that need to be tracked, and have a serial number)

How should one set up a schema for this? The big problem is tracking quantity. Bulk items are tracked by weight, count, etc. Serialized items need to be tracked per item, and the serial number needs to be tracked for it as well.

Now, I can set this up in code, no problem. Something like:

Item   -->   Item Model (type)
   ^
InventoriedItem
   ^               ^                      ^
BulkItem   RegularItem  SerializedItem

The difference between, say "RegularItem" and "SerializedItem", being that RegularItem has a quantity, but no Serial Number, and SerializedItem having no quantity but it does have a Serial Number.

The problem is, how to set this up in the database.

I was leaning towards table inheritance, but was a bit discouraged by this idea from Frans' final post at http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=1456 .

The thought is that there should be a row in a table for each serialized item, but only 1 row (in a different table?) for all other items with a quantity column. I thought about putting 1 row for each item in a table, but that just wouldn't work for bulk items which could be measured by weight and it could be a very high number.

I also want to have simple query access to the "catalog". Someone else coming in shouldn't have to search all over the schema to find the various tables that make up the catalog.

Anyone go through this before? Any comments/advice would be helpful.

Jeff...

I had to do something similar with an AR System once. We had an invoice. An invoice had lines that made up the invoice. Each line had different types, i.e. the value of each line could be calculated using different mechanisms. Also, since it was a double enty accounting system, if a line needed to be changed, we had to create another line, called a journal line, that inessence, voided one of the original lines. So the organization looked like this:

Invoice --Invoice Line ----Journal Line

Now, on the invoice of course, it had to look like this:

Invoice # 1234 Balance Due on Invoice: 80.00

LineName---Item------Qty---Unit Price---Total Line 1--------Widget-----2-----10.00------20.00 Line 1 J------Journal-----2---- -10.00-----20.00 Line2---------WidgetB---4-----20.00------80.00

I ended up with many line tables. One table for each type of line. Then I pulled all lines together in a table called InvoiceLines. All of the line tables related back to the invoice lines.

When I needed to access the entire set of data, I would use a view that compiled all key values into one view. From the view I could find an invoice by account, all lines associated with the invoice, etc. When the operator interacted with a row from the view, I could use the data in the view to determine which type of line I needed to deal with. Then I could invoke a strategy pattern to implement logic for the particular type of line being worked on.

So, in your scenario, you could have a table that held all PK values for all items in the inventory. Then you could use data from this table to determine which inventory items were being dealt with. Once you can determine the type of inventory item being dealt with, you can use a strategy pattern to implement specific logic for that particular type of inventory.

I hope this makes sense.

Most common approach is to draw a supertype/subtype NIAM model with these entities.

(supertype) <---- (subtype)

so in this case: (item) <----- (regular item) (item) <----- (bulk item) (item) <----- (Serialized item)

and you then add the roles and attributes to the types defined. So attributes common to all different itemtypes are added to the supertype (item), and the specific attributes per item type are added to the subtypes.

like: (item)-----[ | ]--------{item type} (serialized item) ---[ | ]-----{serial number} (regular item) ----[ | ]----{quantity}

Looking at your description, I don't think there are a lot of attributes which are shared among the entities, besides all of them being items. Also the logic for processing them is different.

Now, the most efficient approach is to 'flatten' a hierarchy path into a single table. This means that you end up with a table for each specialized item, and each table gets the fields for (item) as well. In your situation this is IMHO the best way to do it, as your item entity doesn't seem to have a lot of shared attributes.

Flattening hierarchies is often doable except when you have a 3 layer or more hierarchy with the lower layer having a relation with another entity. Flattening this would enable the relation also for entities higher in the hierarchy. In your case this isn't true.

Thanks, guys. In terms of using the Strategy Pattern, not sure if it helps me here as the behavior isn't different per type (except maybe in calculating quantity), only the data.

Frans, how would the picture change if the majority of the attributes were common between sub types?

Jeff...

jeffreygg wrote:

Frans, how would the picture change if the majority of the attributes were common between sub types? Jeff...

In that case, it is not wise to flatten hierarchies out, and you end up with a separate table per type with only the type specific fields in that table, connected with 1:1 relations with the parent type table. This gives the least amount of data-overhead. If performance is an issue though (and not data, say the system is not going to contain 1million rows but is queried a lot), the flattening of the hierarchies to 1 table per hierarchy can still be more efficient as that will save you a join.

There are no fixed laws which prescribe you one over the other, it depends on what amounts of data you expect vs. the amount of times you're requesting the data.

Frans said what I was trying to say, in a much cleaner way.

Jeff, in regards to the strategy pattern, it can help you more than you think, here are some samples:

• If you needed to execute different logic for ordering based on type
• executing different logic by type with regards to current levels and re-order levels
• executing different logic by type when handling inventory receipts

Thanks, Frans. So, in this case "table inheritance" might seem to make sense?

Devildog74 wrote:

Jeff, in regards to the strategy pattern, it can help you more than you think, here are some samples:

• If you needed to execute different logic for ordering based on type
• executing different logic by type with regards to current levels and re-order levels
• executing different logic by type when handling inventory receipts

Devildog...that does help me. Thanks.

jeffreygg wrote:

Thanks, Frans. So, in this case "table inheritance" might seem to make sense?

Yes I think it does. Using a 1:1 relation, you can still retrieve the related data in 1 go, save the related entities in 1 go so it's not that big of a deal, but you have to check yourself if it's worth the extra join, based on your estimates of how much data will be in these tables vs. how much queries will be executed on the data.