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:


SKU ProductName
### TABLE 2:
Loc_ID LocationName
### TABLE 3:
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.