A Little Database Magic

Here’s a little puzzle I’ve been thinking about for the past couple of days. I’m sure this has been done before, but it’s the first time I’ve run into it, and it’s been a fun little exercise.

The ACME Company needs to keep track of some inventory. The inventory is kept in several different locations. For each product they need to be able to tell how many units are in which location.

The puzzle is to design a database that handles that information. Ideally, the database can sync the inventory in one or more locations with the database of their online store.

At it turns out the database is pretty simple. You can do it in three tables:

TABLE 1:

Products
SKU
ProductName

TABLE 2:

Locations
Loc_ID
LocationName

TABLE 3:

Quantities
Quant_ID
SKU
Loc_ID
Quantity

The magic happens in Table 3. In Table 3 SKU is keyed to the SKU from Table 1. Loc_ID is keyed to Loc_ID from Table 2. Then you set a multi-column unique index so that any given SKU-Loc_ID combination can appear only once in the table, representing the Quantity of any given product code at any given location.

Now you can search on Table 3 by either location or product. Table 3 automatically expands to accommodate new values whenever a new product is added to Table 1 or a new location is added to Table 2.

I love magic.

Enjoy!

2 thoughts on “A Little Database Magic

  1. Some companies (like mine) don’t want there to be a single quantity in a location, because the parts are serialized. If I want to find SKU: ABC with SN: 123, I’ll need to know where to look for that specific object among many of the same SKUs in multiple locations.

    • It sounds like you’re actually tracking by serial number then, not SKU, or perhaps a unique SKU/serial number combination. That’s a different problem (but it doesn’t sound too hard).

      For the “ACME Company” problem above, all items of a given SKU are identical. However, they can only ship online orders from one location, so they need to know how many of item X they have at location Y and how many they have in other locations to move to location Y if necessary.

Comments are closed.