§6 · Data model — Sample audit

6.1 Approach

This section describes the logical data model as observed in the production database. It is grouped by domain rather than by table-name prefix; the same vendor uses several naming conventions across modules and a strict table-by-table reading is harder to comprehend than a domain-by-domain reading.

The model below covers 22 core entities. The full database contains 280+ tables; the remaining ~260 are configuration, lookup, log, and intermediate-staging tables that participate in but do not define the system’s semantics. Their existence is acknowledged in §6.7.

6.2 Entity inventory

#	Entity	Domain	Row count (approx.)	Notes
1	Item	Master	14 000	The central nominal entity. Linked to almost everything.
2	Customer	Master	600	Includes 80 “dormant” customers (no transactions in 5+ years).
3	Supplier	Master	280	Includes 40 dormant.
4	Bill of material (BOM) header	Master	3 200	One BOM per manufactured item, mostly. ~150 items have multiple BOMs (versioned).
5	BOM line	Master	28 000
6	Routing header	Master	1 800
7	Routing operation	Master	11 500
8	Work centre	Master	35
9	Warehouse / location	Master	12 warehouses, ~180 bin locations
10	Customer order header	Transactional	480 / week (78 000 lifetime active + closed)
11	Customer order line	Transactional	1 200 / week
12	Production order header	Transactional	120 / day (28 000 active + recently-closed)
13	Production order line / operation	Transactional	~360 / day
14	Production event	Transactional	~2 500 / day	Pickup, completion, scrap events.
15	Purchase order header	Transactional	25 / week
16	Purchase order line	Transactional	75 / week
17	Goods receipt header / line	Transactional	30 / day
18	Inventory transaction	Transactional	~3 000 / day	The system’s audit-of-record for stock movements.
19	Supplier invoice	Transactional	80 / week
20	Sales invoice	Transactional	~400 / week
21	GL journal	Transactional	~6 000 / month	One row per posting line.
22	Period	Control	12 / year × 17 years = 204	The close-state ledger.

6.3 ERD — core relationships

A compressed entity-relationship summary, focused on cardinality and on the relationships that participate in the highest-traffic queries. → denotes “many to one in the direction of the arrow”.

                    ┌───────────────┐
                    │     Item      │◀─────────────────┐
                    │  (14 000)     │                  │
                    └───────┬───────┘                  │
                            │                          │
        ┌───────────────────┼────────────────────┐     │
        │                   │                    │     │
        ▼                   ▼                    ▼     │
   ┌──────────┐      ┌──────────────┐    ┌─────────────┴────┐
   │ BOM line │──┐   │ Routing op   │──┐ │ Cust. order line │
   └──────────┘  │   └──────────────┘  │ └─────────┬────────┘
                 ▼                     ▼           │
            ┌────────────┐    ┌──────────────┐     │
            │ BOM header │    │ Routing hdr  │     │
            │  (3 200)   │    │  (1 800)     │     │
            └─────┬──────┘    └──────┬───────┘     │
                  │                  │             │
                  └────────┬─────────┘             │
                           ▼                       ▼
                  ┌─────────────────┐    ┌────────────────────┐
                  │ Production      │◀───┤ Customer order hdr │
                  │ order header    │    │      (480/wk)      │
                  │  (120/day)      │    └────────────────────┘
                  └────────┬────────┘
                           │
                           ▼
                  ┌─────────────────┐    ┌────────────────────┐
                  │ Production      │    │ Inventory trans    │
                  │ order line/op   │───▶│ (~3 000/day)       │
                  │  (~360/day)     │    └────────────────────┘
                  └────────┬────────┘             ▲
                           │                      │
                           ▼                      │
                  ┌─────────────────┐             │
                  │ Production event│─────────────┘
                  │  (~2 500/day)   │
                  └─────────────────┘

The cardinality pattern is conventional manufacturing ERP. Two relationships warrant explicit note:

Item → BOM header is 1:0..n. Most items have zero BOMs (purchased) or one (manufactured). About 150 items have multiple BOMs, distinguished by bom_version. The current_bom_id pointer on the item resolves which is in force; this is enforced at the application layer, not at the database layer. [INF]
Production order line → Inventory transaction is 1:n. Each production event of type = completion writes both a production_event row and one or more inventory_transaction rows (one per material consumed plus one for finished output). This double-write is transactional via stored procedure.

6.4 Financial sub-model

   ┌───────────────────┐      ┌──────────────────┐
   │ Customer invoice  │      │ Supplier invoice │
   │  (~400/wk)        │      │   (~80/wk)       │
   └─────────┬─────────┘      └────────┬─────────┘
             │                         │
             └────────────┬────────────┘
                          ▼
                ┌───────────────────┐
                │   GL journal      │
                │  (~6 000/month)   │
                └─────────┬─────────┘
                          │
                          ▼
                ┌───────────────────┐         ┌──────────────────┐
                │     Period        │────────▶│ Statutory report │
                │  (12/yr × 17yr)   │         │   (off-system)   │
                └───────────────────┘         └──────────────────┘

Customer invoices originate from sales (linked back to customer orders and goods issues). Supplier invoices originate from purchasing (linked to PO + GR). Both post to the GL journal. The GL journal is partitioned by period; closed periods are read-only at the database level (enforced by trigger). [DB]

6.5 Master-data integrity

The audit ran targeted integrity queries against the production database. Findings:

Check	Expected	Observed	Status
Items with no BOM and `make_or_buy = 'M'`	0	8	discrepancy — likely setup errors; production blocked when these are ordered. [DB]
Active customers with no transactions in 36 months	flagged dormant	12 (not flagged)	drift — dormant-flag housekeeping has not been done since 2022.
Suppliers with no IBAN	0	14	These are old cash/cheque suppliers; cannot be paid via bank file. [DB]
Items referenced by closed production orders but no longer in `items`	0	0	OK — soft-delete is honoured.
BOM cycles (item references self transitively)	0	0	OK.
Routing operations referencing non-existent work centres	0	3	discrepancy — work centre 37, retired in 2020, still referenced by 3 inactive routings. Cosmetic but indicates housekeeping gap.
Customer-order lines with `delivery_date IS NULL` and status not in `('cancelled','draft')`	0	7	See §4.4 anomaly — these correspond to operator-edits that should not have been possible.
Inventory transactions with `quantity = 0`	0	1 142	Reversal/correction pattern. Not a defect; documented in vendor manual.

6.6 Data-volume profile and growth

The database is approximately 340 GB on disk. By table family:

Family	Approx. size	Growth/year	Notes
Inventory transactions	110 GB	12 GB	Largest table; partitioned by year since 2018.
Production events	70 GB	8 GB	Second largest.
GL journal	35 GB	3 GB
Customer / sales invoices	28 GB	2 GB	Includes printed-invoice PDFs stored in-row [INF, CFG].
Audit / activity log	22 GB	2.5 GB	Purged at 24 months.
Master data	4 GB	< 100 MB
Crystal Reports definitions	3 GB	< 50 MB
Other / system	~68 GB	varies	Includes a 2018 schema-version copy that was never dropped. [DB]

Annual growth is ~30 GB. The 2018 schema-version copy (~40 GB) is dead weight; dropping it is a Patch-tier action (see §12). The customer’s IT is aware but has not acted because “it works.”

6.7 What lies outside the 22-entity core

The 280-table full schema includes:

~30 configuration tables — application settings, screen-layout overrides, report parameters
~50 lookup / reference tables — unit-of-measure conversions, country codes, currency, VAT rates
~40 staging / intermediate tables for batch jobs (MRP intermediate, period-close worktables)
~80 audit and log tables
~30 integration tables — broker queues, SSIS staging, EDI envelopes
~30 customer-specific tables added by the customer’s IT or by the consultant; these are the highest-risk for modernisation because their semantics live partly in stored procedures and partly in oral tradition

The customer-specific tables are inventoried in Appendix C (see §14).