Inventory Architecture in Database

Question

Hello,

This is not a question related to a specific language, rather on the correct methodology of architectural of handling inventory.

Consider the following structure for storing software:

platforms (platformID*, platformName)
titles (titleID*, titleName)

And the following is a joiner table for unique products

products (platformID*, titleID*, releaseDate, notes)

And the following is a table I would like to keep track of my inventory

inventory (platformID*, titleID*, quantityAvailable)

In examples I have seen, others have created a table where each unique copy of a software is stored in a separate line as such:

software(softwareID*, softwareTitle)
inventory(inventoryID*, softwareID*)

I would like to know which approach is preferable? Should I create an inventoryID and thus a row for each unique software copy:

inventory(inventoryID*, platformID*(fk), titleID*(fk))

Or use a table that stores quantity instead, as such:

inventory(platformID*(fk), titleID*(fk), quantityAvailable)

Many thanks!

Answer 1

Should I create an inventoryID and thus a row for each unique software copy?

There is no reason to do this, unless you want to store some information on each unique software copy, such as the date each copy was purchased. This is rarely pracitical in the inventory of software.

Or use a table that stores quantity instead?

You can also consider adding a quantityAvailable column in your products table, unless you think that eventually you'd want to have many inventories for each title, in order to be able to allocate a quantity of stock that is under special offer, that is soon going to expire, etc.

Answer 2

The advantage of having a unique row for each piece of inventory is that later if you want to keep track of things like inventory that's on layover, inventory that's on preorder, inventory that's been sold but could still be returned, etc.

I don't see any real disadvantage to this approach except that it's probably more work which might not pay off if these things aren't really needed.

Answer 3

I also would start with quantityAvailable instead of lines for all the items. But I would still opt for an inventoryId, since cases could occur, where you have to dissect the entries with the same platform/title combination -- with an inventoryId you are more enhanceable in the future.

I would also recommend to add a further column: versionNo --- the version number of a software product. Sometimes you might have differing versions of the same product. When you have this, it is not a good idea to drop the information into the title (for example you want to search for all "Microsoft Office" products regardless of version ...).