# Data Dictionary: [System or data domain name]

> Business and technical definitions for every entity and attribute in a
> system or data product: meaning, type, format, allowed values, source,
> owner and classification, plus code lists, derived fields and a change
> log. Based on common industry practice for data dictionaries and
> metadata management. Replace every `[bracketed]` value and every row
> marked *Example*.

<!--
Guidance: How to use this template
- Write the business definition first, in words a business user would
  agree with, then add the technical attributes. A dictionary that only
  repeats column names and types adds nothing to the schema.
- One row per attribute. If two systems use the same name with different
  meanings, give them different entries and say how they differ.
- Classify every attribute (section 1.3). Unclassified personal data is the
  most common audit finding.
- Keep the dictionary next to the schema and update both in the same change.
-->

| Field | Value |
|---|---|
| Document ID | [DD-ORDERS-001] |
| System / data product | [Orders database, schema `public`] |
| Version | [0.1] |
| Status | [Draft / In review / Approved] |
| Data owner | [@data-owner] |
| Data steward | [@data-steward] |
| Last updated | YYYY-MM-DD |
| Related | [Database design document], [Data model diagram], [Glossary] |

## 1. Conventions

<!-- Guidance: Explain how to read the tables below so that every entry is filled the same way. -->

### 1.1 Column meanings

| Column | Meaning |
|---|---|
| Attribute | Physical name as it appears in the schema or file |
| Business name | Name a business user would recognise |
| Definition | What the value means, in one or two sentences, without repeating the name |
| Type and length | Physical data type including precision or maximum length |
| Format | Pattern, unit, time zone or encoding |
| Allowed values | Range, list or reference to a code list in section 4 |
| Null | `No` if a value is always present; otherwise when it is absent |
| Default | Value used when none is supplied |
| Source | System, form field or calculation that produces the value |
| Owner | Person or team accountable for the meaning and quality |
| Class. | Classification label from 1.3 |
| Example | A realistic value |

### 1.2 Naming and formatting rules

- Attribute names are snake_case; business names are Title Case.
- Dates use ISO 8601 (`YYYY-MM-DD`); timestamps are UTC (`YYYY-MM-DDThh:mm:ssZ`).
- Money is stored as a decimal amount plus an ISO 4217 currency code; never as a float.
- Country codes are ISO 3166-1 alpha-2; language codes are BCP 47.
- Boolean attributes start with `is_` or `has_`.

### 1.3 Classification labels

| Label | Meaning | Handling |
|---|---|---|
| Public | May be published | No restriction |
| Internal | For employees and contractors | Access by role |
| Confidential | Business-sensitive | Need-to-know; encrypted at rest |
| Personal | Identifies or relates to a person | Privacy notice applies; masked outside production |
| Sensitive personal | Special category or financial account data | Strictly limited; field-level protection |

## 2. Entity list

<!-- Guidance: One row per entity or table. The definition says what one row represents. -->

| Entity | Business name | One row represents | Owner | Record count (approx.) | Retention |
|---|---|---|---|---|---|
| `orders` | Order | One customer order, from checkout to completion or cancellation | Orders team | *Example:* 70 million | 10 years |
| `order_line` | Order Line | One product and quantity within an order | Orders team | 175 million | 10 years |
| `customer_ref` | Customer Reference | A customer known to the Orders domain (ID and display name only) | Customer team | 4 million | While active plus 2 years |
| `order_event` | Order Status Change | One change of an order's status | Orders team | 280 million | 7 years |
| [entity] | [Business name] | [Row meaning] | [Owner] | [Count] | [Retention] |

## 3. Attributes by entity

<!-- Guidance: One table per entity. Fill every column; write "n/a" with a reason rather than leaving a cell empty. -->

### 3.1 `orders` (Order)

| Attribute | Business name | Definition | Type and length | Format | Allowed values | Null | Default | Source | Owner | Class. | Example |
|---|---|---|---|---|---|---|---|---|---|---|---|
| `order_id` | Order ID | System identifier of the order, never shown to customers | uuid | UUID v7 | Any | No | Generated | Orders service | Orders team | Internal | `0190f3a2-7c1e-7b2a-9d4f-3e5b6a7c8d9e` |
| `order_number` | Order Number | Number printed on invoices and quoted by customers | varchar(20) | `ORD-YYYY-NNNNNN` | Unique | No | Generated | Orders service | Orders team | Internal | `ORD-2026-000123` |
| `customer_id` | Customer ID | Customer who placed the order | uuid | UUID | Must exist in `customer_ref` | No | none | Checkout | Customer team | Personal | `0190f39e-11aa-7c3d-8e9f-0a1b2c3d4e5f` |
| `status` | Order Status | Current position of the order in its lifecycle | varchar(30) | Upper snake case | Code list CL-01 | No | `PENDING_PAYMENT` | Orders service | Orders team | Internal | `SHIPPED` |
| `currency` | Order Currency | Currency in which all amounts of the order are expressed | char(3) | ISO 4217 | CL-03 | No | none | Storefront | Finance | Internal | `EUR` |
| `total_amount` | Order Total | Amount payable for all lines including tax, before refunds | numeric(12,2) | Decimal, 2 places | 0 to 999 999 999.99 | No | 0 | Derived (DF-01) | Finance | Confidential | `39.80` |
| `channel` | Sales Channel | Channel through which the order was placed | varchar(20) | Upper snake case | CL-02 | No | `WEB` | Checkout | Commerce team | Internal | `MOBILE_APP` |
| `created_at` | Order Date and Time | Moment the customer confirmed checkout | timestamptz | ISO 8601 UTC | Not in the future | No | now() | Orders service | Orders team | Internal | `2026-03-14T09:30:00Z` |
| [attribute] | [Name] | [Definition] | [Type] | [Format] | [Values] | [Y/N] | [Default] | [Source] | [Owner] | [Class.] | [Example] |

### 3.2 `order_line` (Order Line)

| Attribute | Business name | Definition | Type and length | Format | Allowed values | Null | Default | Source | Owner | Class. | Example |
|---|---|---|---|---|---|---|---|---|---|---|---|
| `order_line_id` | Order Line ID | System identifier of the line | uuid | UUID v7 | Any | No | Generated | Orders service | Orders team | Internal | `0190f3a3-02b4-7d5e-8f60-71829304a5b6` |
| `order_id` | Order ID | Order the line belongs to | uuid | UUID | Must exist in `orders` | No | none | Orders service | Orders team | Internal | `0190f3a2-7c1e-7b2a-9d4f-3e5b6a7c8d9e` |
| `sku` | Stock Keeping Unit | Catalogue code of the product variant sold | varchar(40) | Upper case, hyphenated | Must exist in catalogue at order time | No | none | Catalogue | Catalogue team | Internal | `TSHIRT-BLK-M` |
| `quantity` | Quantity | Number of units ordered | integer | Whole number | 1 to 999 | No | none | Checkout | Orders team | Internal | `2` |
| `unit_price` | Unit Price | Price of one unit including tax at the time of the order | numeric(12,2) | Decimal, 2 places | 0 or more | No | none | Pricing service | Pricing team | Confidential | `19.90` |
| `line_total` | Line Total | Amount for this line | numeric(12,2) | Decimal, 2 places | 0 or more | No | Derived | Derived (DF-02) | Finance | Confidential | `39.80` |
| [attribute] | [Name] | [Definition] | [Type] | [Format] | [Values] | [Y/N] | [Default] | [Source] | [Owner] | [Class.] | [Example] |

### 3.3 `[entity]` ([Business name])

[Repeat the attribute table for every entity in section 2.]

## 4. Code lists and enumerations

<!-- Guidance: Every attribute whose allowed values are a list points to one of these tables. Record the meaning of each code and whether it is still in use. -->

### 4.1 CL-01 Order status

| Code | Label | Meaning | Active | Since |
|---|---|---|---|---|
| `PENDING_PAYMENT` | Pending payment | Order created; payment not yet authorised | Yes | v1.0 |
| `PAID` | Paid | Payment authorised; ready for fulfilment | Yes | v1.0 |
| `SHIPPED` | Shipped | At least one shipment handed to a carrier | Yes | v1.0 |
| `DELIVERED` | Delivered | All shipments delivered | Yes | v1.0 |
| `CANCELLED` | Cancelled | Cancelled before shipment; no further changes | Yes | v1.0 |
| `ON_HOLD` | On hold | *Example:* Fraud review; replaced by a separate flag | No (retired YYYY-MM-DD) | v1.0 |

### 4.2 CL-02 Sales channel

| Code | Label | Meaning | Active |
|---|---|---|---|
| `WEB` | Website | Desktop or mobile browser storefront | Yes |
| `MOBILE_APP` | Mobile app | iOS or Android app | Yes |
| `CALL_CENTRE` | Call centre | Order taken by an agent | Yes |
| `MARKETPLACE` | Marketplace | Order imported from a third-party marketplace | Yes |

### 4.3 CL-03 Currency

[Reference the external code list instead of copying it. Example: "ISO 4217
alphabetic codes; accepted subset: EUR, GBP, USD, CHF, SEK."]

## 5. Derived fields

<!-- Guidance: Give the exact formula, the inputs, rounding rules and when the value is recalculated. Ambiguous derivations cause most reporting disputes. -->

| ID | Attribute | Formula | Inputs | Rounding | Recalculated when |
|---|---|---|---|---|---|
| DF-01 | `orders.total_amount` | Sum of `line_total` over the order's lines | `order_line.line_total` | Half-up to 2 decimals after summing | Any line is added, removed or changed |
| DF-02 | `order_line.line_total` | `quantity` x `unit_price` | `quantity`, `unit_price` | Half-up to 2 decimals | Generated on write |
| DF-03 | `average_order_value` (report) | Sum of `total_amount` / count of orders, excluding `CANCELLED` | `orders` | 2 decimals | Daily batch |
| [DF-nn] | [Attribute] | [Formula] | [Inputs] | [Rule] | [Trigger] |

For report metrics, show the formula explicitly. Average order value for a
period $P$, over non-cancelled orders $O_P$:

$$
\text{AOV}_P = \frac{\sum_{o \in O_P} \text{total\_amount}_o}{|O_P|}
$$

## 6. Data quality rules

<!-- Guidance: Rules that can be tested automatically. Link each one to the attribute and the owner who fixes failures. -->

| ID | Attribute(s) | Rule | Threshold | Check frequency | Owner |
|---|---|---|---|---|---|
| DQ-01 | `orders.total_amount` | Equals DF-01 recomputed | 100% | Nightly | Finance |
| DQ-02 | `orders.customer_id` | Exists in `customer_ref` | 100% | Nightly | Customer team |
| DQ-03 | `order_line.sku` | Exists in catalogue history | 99.99% | Daily | Catalogue team |
| [DQ-nn] | [Attribute] | [Rule] | [Threshold] | [Frequency] | [Owner] |

## 7. Change log

<!-- Guidance: Record every added, changed or retired attribute and code, with the schema migration or release that carries it. Consumers read this table to find breaking changes. -->

| Version | Date | Entity / attribute / code | Change | Reason | Migration or release | Author |
|---|---|---|---|---|---|---|
| 0.1 | YYYY-MM-DD | All | Initial dictionary | First release | n/a | [@name] |
| 0.2 | YYYY-MM-DD | *Example:* `orders.channel` | Added with code list CL-02 | Channel reporting | `V014` | [@name] |
| 0.3 | YYYY-MM-DD | *Example:* CL-01 `ON_HOLD` | Retired | Replaced by fraud review flag | Release 2.3 | [@name] |
| [x.y] | YYYY-MM-DD | [Item] | [Added / Changed / Retired] | [Reason] | [Reference] | [@name] |

## Standard and licence note

Based on common industry practice for data dictionaries and metadata
management, using ISO 8601, ISO 4217 and ISO 3166 codes by reference only.
Original text written for this collection; example values are
illustrative. Template licence: MIT.