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:
|Quant_ID SKU Loc_ID Quantity|
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.