Products & SKUs

The three-tier SKU model (Base, Product, and Case SKUs), plus categories and the bill-of-materials junction that ties them together.

This page covers the catalog tables. National Commerce models a product in three tiers: a Base SKU is the physical inventory unit, a Product SKU is the sellable Amazon listing (often a multi-pack of base units), and a Case SKU is a case of base units used for replenishment. A junction table records the bill of materials between Product SKUs and Base SKUs.

Which SKU is which?

A Base SKU is what inventory is counted against. A Product SKU is what Amazon sells; it bundles one or more base units (Bundle_Count per pack). A Case SKU packs a fixed quantity of base units for purchase orders. See the glossary for the full definitions: Base SKU, Product SKU, Case SKU.

Base SKUs

The physical inventory unit. Inventory is tracked against a Base SKU's inventory_sku. Each Base SKU belongs to one Brand.

Column	Type	Notes
`id`	integer	Primary key. Default from sequence `"Base Item SKUs_id_seq"` (a legacy rename).
`inventory_sku`	text	The SKU that inventory is tracked against.
`inventory_name`	text	Denormalized item name.
`Activity_Status`	text	Default `'Active'`.
`Product_Weight__lbs_`	numeric	Unit weight in pounds.
`Product_Weight__Oz_`	numeric	Unit weight in ounces.
`order_multiple`	bigint	Smallest reorder increment. Default `1`.
`brand_id`	integer	→ references Brands.
`brand_name`	text	Denormalized brand name.
`created_at`, `updated_at`, `created_by`, `updated_by`	timestamp / varchar	Standard audit columns.

Automation. brand_name is kept in sync with the linked Brand by triggers (update_brand_name_for_base_sku on update, update_brand_name_for_base_sku_2 on insert). Changes to order_multiple are pushed down to matching Inventory rows by sync_base_sku_order_multiple_with_inventory (after update).

Base SKUs are the hub of the model: Product SKUs, Case SKUs, and the junction all point back to them, and Inventory is tracked against their inventory_sku.

Product SKUs

The sellable Amazon listing. A Product SKU usually represents a multi-pack of one Base SKU, with Bundle_Count units per pack. It carries listing, packing, and shipping metadata.

Column	Type	Notes
`id`	integer	Primary key. Default from sequence `"Product SKUs_id_seq"`.
`Product_SKU`	text	The listing's SKU string.
`ASIN`	text	Amazon product identifier.
`Brands_id`	integer	→ references Brands.
`Base Item SKUs_id`	integer	→ references Base SKUs. The base unit this listing is built from.
`product_category_id`	bigint	→ references product_categories.
`Bundle_Count`	bigint	Units per pack. Default `1`.
`product_name`	text	Listing name.
`product_name_2`	text	Default is a generated-style expression: `inventory_name
`Abbreviated_Product_Title`	text	Short title.
`inventory_name`	text	Denormalized base item name.
`brand_name`	text	Denormalized brand name.
`Packing_Weight__lbs_`	numeric	Packing weight in pounds.
`Packing_Weight__Oz_`	numeric	Packing weight in ounces.
`Shipping_Weight__Oz_`	numeric	Shipping weight in ounces.
`Shipping_Dimensions__LWH_`	varchar	Length/width/height.
`ShipHero_Box_Name`	text	Box used in ShipHero.
`Product_tag_in_Shiphero`	text	ShipHero product tag. See warning below.
`Product_Tag_in_ShipHero`	text	Near-duplicate ShipHero product tag. See warning below.
`Postage_Threshold`	numeric	Underlies postage threshold tracking.
`created_at`, `updated_at`, `created_by`, `updated_by`	timestamp / varchar	Standard audit columns.

Duplicate ShipHero tag columns

This table has two columns that differ only in capitalization: Product_tag_in_Shiphero and Product_Tag_in_ShipHero. They are distinct columns. Confirm which one your code reads and writes before relying on either.

Automation. brand_name is kept in sync with the linked Brand by update_brand_name_for_product_sku (after update) and _2 (after insert). inventory_name is synced onto the linked Base SKU by update_base_sku_inventory_name_for_product_sku (after update) and _2 (after insert).

Each Product SKU links to one Base SKU (via Base Item SKUs_id), one Brand, and optionally one product_categories row. Its full bill of materials lives in the junction table.

Case SKUs

A case packs a fixed quantity of base units for replenishment. Case SKUs are used to build inventory replenishment purchase orders.

Column	Type	Notes
`id`	integer	Primary key. Default from sequence `"Case SKUs_id_seq"`.
`case_sku`	text	The case SKU string. `NOT NULL`.
`base_sku`	integer	→ references Base SKUs. The base unit packed into the case.
`base_sku_quantity`	integer	Base units per case. Default `12`.
`brand_id`	integer	→ references Brands.
`product_name`	text	Case name.
`created_at`, `updated_at`, `created_by`, `updated_by`	timestamptz / timestamp / varchar	Standard audit columns.

Each Case SKU references one Base SKU and one Brand. See Purchase Orders for how cases feed replenishment.

product_categories

Product categories. Each category belongs to a single Brand, and Product SKUs reference categories through product_category_id.

Column	Type	Notes
`id`	bigint	Primary key. `GENERATED ALWAYS AS IDENTITY`.
`name`	text	Category name. `NOT NULL`.
`brand_id`	integer	→ references Brands. `NOT NULL`.
`created_at`, `updated_at`, `created_by`, `updated_by`	timestamptz / varchar	Standard audit columns.

A category belongs to exactly one Brand. Product SKUs point to a category via their product_category_id.

product_sku_base_sku_junction

The bill of materials: which Base SKUs, and how many of each, make up a Product SKU. Each row links one Product SKU to one Base SKU with a per-product quantity.

Column	Type	Notes
`id`	bigint	Primary key. `GENERATED ALWAYS AS IDENTITY`.
`product_sku`	integer	→ references Product SKUs. `NOT NULL`.
`base_sku`	integer	→ references Base SKUs. `NOT NULL`.
`brand`	integer	→ references Brands. `NOT NULL`.
`bundle_count`	numeric	Units of that base per product. `NOT NULL`.
`created_at`	timestamptz	Default `now()`.

A Product SKU can be composed of one or more Base SKUs; each pairing is one row here, with bundle_count recording how many base units that product includes.

product_skus_used

A small standalone lookup/tracking table of product SKU strings. It has no foreign keys and is not tied into the rest of the catalog model.

Column	Type	Notes
`id`	integer	Primary key. `NOT NULL`.
`product_sku`	text	A product SKU string.

This table stands on its own; treat it as a simple list of SKU strings rather than part of the relational catalog.