Dimension table of stocked products
| Column | Type | Key |
|---|---|---|
| product_id | INTEGER | PK |
| sku | VARCHAR | |
| name | VARCHAR | |
| category | VARCHAR | |
| unit_cost | DECIMAL |
Dimension table of warehouse locations
| Column | Type | Key |
|---|---|---|
| warehouse_id | INTEGER | PK |
| name | VARCHAR | |
| region | VARCHAR |
Fact table of periodic on-hand stock counts by product and warehouse
| Column | Type | Key |
|---|---|---|
| snapshot_id | INTEGER | PK |
| product_id | INTEGER | FK → products.product_id |
| warehouse_id | INTEGER | FK → warehouses.warehouse_id |
| snapshot_date | DATE | |
| quantity_on_hand | INTEGER | |
| quantity_reserved | INTEGER | |
| reorder_point | INTEGER |