CHAPTER 03Data Model
Req. prefix: DMv0.4 draft
Entities are specified logically (attributes and constraints), not physically. Any storage technology
satisfying the constraints and the performance obligations of Ch. 06 is acceptable.
Read-only master data (§3.1–3.6 — masters, calendar, forecast, split tables, phase profiles) is
synthetically generated by the seed-profile generator (Ch. 10) and changes wholesale only via seed
selection/reset or forecast regeneration (DE-08); there is no ingestion and no editing (D-23).
Application state (§3.7–3.8) is owned by HiLo.FM and managed through its interfaces.
3.1Product master: hierarchy & attributes
The product dimension has exactly two hierarchy levels: UPC → Product ID.
The UPC (Universal Product Code) is the externally defined article; a UPC contains one or more
Product IDs — the finished products actually produced. Everything else that used to look like a
hierarchy (brand, category, subcategory, sector, …) is modelled as attributes of the product,
because such classifications do not nest cleanly (one brand spans categories) and therefore cannot form a tree.
| Attribute | Type | Notes |
|---|
product_id | string, key | Base-grain identifier: the finished product |
upc_id | string | parent UPC; mandatory, exactly one |
name | string | display only |
attributes | map attribute → value | e.g. brand=B1, category=C-A, sector=S2; open set of attribute names |
DM-01The product hierarchy MUST be a strict two-level tree: every product
belongs to exactly one UPC; a UPC contains one or more products. An entry's product scope is a single
attribute=value pair per DM-02; scoping to a UPC or a single product uses the same mechanism via
the built-in attributes.
DM-02Product scope = exactly one attribute=value pair.
Attributes are flat name→value classifications; the built-in fields upc and
product_id are addressable as attributes through the identical mechanism. The pair selects a
product set: product_id=P2 → one product; upc=U2 → that UPC's products;
brand=B1 → all products carrying the value, spanning any number of UPCs. No AND-combinations,
no multi-value (OR) conditions, no product lists: a volume across two brands is simply two entries (HE-05
permits free overlap). Deliberate demonstrator minimalism — richer scope UX can be layered on top of the
fast core later. Attribute-scoped entries are disaggregated via the attribute→UPC stage (Ch. 05/06);
scopes on upc / product_id skip the stages they are already at or below (DF-01).
[Q-12 resolved → D-25]
3.2Location & customer masters
DM-03Locations are a flat list of location_id (+ name). No location hierarchy. An entry scope names one location or all.
DM-04Customers are a flat list of customer_id (+ name). Customer appears only in entry scopes, split tables and phase profiles — never in forecast, never in LO.
3.3Planning calendar
| Attribute | Type | Notes |
|---|
week_id | string, key | e.g. 2026-W14; totally ordered |
month_id | string | e.g. 2026-M04 |
quarter_id | string | e.g. 2026-Q2 |
DM-05The calendar is the sole authority on which weeks constitute a month or quarter. The system MUST NOT derive period membership from civil-calendar arithmetic.
DM-06The calendar MUST cover the full planning horizon. Entries referencing
periods outside it are rejected at validation (HE-04); the seed generator MUST NOT produce forecast or rule
rows outside it.
3.4Forecast
| Attribute | Type | Notes |
|---|
product_id, location_id, week_id | composite key | base grain; defined at Product ID level and aggregable to UPC and to any attribute value |
quantity_su | decimal ≥ 0 | generated values may be fractional; HiLo.FM outputs are integer |
DM-07The forecast is sparse: absent rows mean zero. It is read-only and
synthetically generated (Ch. 10); it changes wholesale only via seed selection/reset or forecast
regeneration (DE-08), each followed by a batch run (Ch. 06). A brand-new product (NPI) typically has no
forecast rows at all; its volume arises from the phase profile of its UPC (§3.6, DR-05).
3.5Location split tables (week-range form)
| Attribute | Type | Notes |
|---|
product_id | string | base-product level |
customer_id | string, nullable | null = generic (non-customer) rule |
week_from, week_to | week ids, inclusive | ranges are the native form; expanded to weeks internally |
location_id | string | |
fraction | decimal (0…1] | |
DM-08After range expansion, for every populated key
(product, customer, week) the fractions MUST sum to 1.0 exactly (tolerance 1e-9), and ranges for
the same key MUST NOT overlap. These are invariants the seed generator MUST guarantee; the engine MAY assert
them and fail loudly (naming the offending key) if violated.
DM-09Split-table resolution for a given product/week within an entry: (1) if the entry is customer-scoped and a matching customer row set exists → use it; (2) else if a null-customer row set exists → use it; (3) else → no split table applies; the default basis of Ch. 05 is used.
3.6Phase profiles (NPI / EOL)
A phase profile declares, per UPC, how volume is divided among that UPC's products over time —
the mechanism of new-product introduction (a product ramping up from zero) and end-of-life
(a product ramping down to zero). It is the product-dimension counterpart of the location split table.
| Attribute | Type | Notes |
|---|
upc_id | string | profile owner |
customer_id | string, nullable | null = generic; customers may have different introduction schedules |
week_from, week_to | week ids, inclusive | native week-range form |
product_id | string | MUST belong to the UPC |
fraction | decimal [0…1] | share of the UPC volume in those weeks |
DM-14After range expansion, for every populated key
(upc, customer, week) the fractions MUST sum to 1.0 exactly (tolerance 1e-9), without overlapping
ranges for the same key — generator invariants as in DM-08. Products of the UPC not listed for a covered week
have share 0 there.
DM-15Phase-profile resolution for a given UPC/week within an entry mirrors
DM-09: (1) customer-scoped entry with matching customer rows → use them; (2) null-customer rows → use them;
(3) no rows for this UPC/week → the UPC→product split falls back to forecast proportions among the UPC's
in-scope products (DR-05). Profiles therefore exist only where phasing genuinely happens; stable
products need no profile.
DM-16A profile MAY cover only part of the horizon. Weeks outside all its
ranges use the fallback of DM-15(3). No requirement to maintain profiles for steady state.
3.7HI store — entries
Full semantics in Ch. 04; attributes here for completeness.
| Attribute | Type | Notes |
|---|
entry_id | string, key | system-assigned, stable |
title | string | e.g. "B1 spring campaign" |
product_scope | single attribute=value pair | per DM-02; upc and product_id are built-in attributes |
location_scope | location_id or ALL | |
customer_scope | customer_id, nullable | |
period | week / month / quarter id | quarter is the coarsest supported |
value_su | integer ≥ 0 | the modification volume |
state | enum | Disaggregated | InError | RequiresDisaggregation |
error_message | string, nullable | populated iff InError |
created_by, created_at, updated_at | — | attribution only; no history (no audit trail) |
3.8LO store — disaggregated rows
| Attribute | Type | Notes |
|---|
entry_id | string | parent entry (GL-02) |
product_id, location_id, week_id | composite | base grain |
quantity_su | integer ≥ 0 | rounded per Ch. 06 |
DM-10Key of the LO store: (entry_id, product_id, location_id, week_id). Rows of different entries on the same base cell coexist (OV-07).
DM-11LO rows with quantity 0 SHOULD NOT be stored. Absence means zero.
DM-12Replacing an entry's disaggregation MUST be atomic per entry: readers see either the previous complete row set or the new one, never a mixture.
DM-13Reporting aggregates the LO store only (any grouping of UPC/product, attribute values, location, week/month/quarter, entry). HI values are never summed for reporting.
3.9Entity relationships
UPC ─< Product ──< Forecast >── Location, Week
│ └─ attributes (brand, category, …; built-in: upc, product_id) — flat, non-nesting
└──< PhaseProfile >── Product, WeekRange, [Customer]
Product ──< SplitTable >── Location, WeekRange, [Customer]
Entry ── scope: attribute=value · Location|ALL · [Customer] · Period(Week|Month|Quarter)
Entry ─< LO row >── Product, Location, Week Calendar: Week >── Month >── Quarter