Tables, Relational Logic, and Data Modeling

Introduction

Supply-chain decisions depend on data that are distributed across products, suppliers, customers, locations, orders, shipments, inventory records, and transactions. A spreadsheet may begin as a simple list, but it quickly becomes unreliable when users combine repeated descriptions, inconsistent identifiers, manually entered calculations, and multiple versions of the same record. Data modeling provides a disciplined way to represent these entities and their relationships so that information remains accurate, traceable, reusable, and suitable for analysis.

This chapter develops a practical relational approach for spreadsheet-based supply-chain work. It explains how to design structured tables, distinguish entities from events, use primary and foreign keys, model one-to-many and many-to-many relationships, apply normalization concepts, and construct pivot-table analyses. It also introduces calculated fields and reusable semantic definitions—shared definitions of measures such as on-time delivery, inventory turnover, and fill rate.

Although examples use spreadsheet terminology, the underlying ideas apply to relational databases, enterprise resource-planning systems, data warehouses, and modern analytics platforms.

Learning Objectives

By the end of this chapter, you should be able to:

1. Design structured tables with one row per appropriate entity or event.
2. Distinguish attributes, measures, identifiers, and transactional facts.
3. Select and validate primary keys and foreign keys.
4. Represent one-to-one, one-to-many, and many-to-many relationships.
5. Apply first, second, and third normal-form reasoning to spreadsheet designs.
6. Build pivot tables that aggregate data without double counting.
7. Create calculated fields and explain their assumptions.
8. Define reusable business metrics with consistent semantics.
9. Diagnose common spreadsheet data-modeling failures.
10. Evaluate governance, ethical, and operational implications of model design.

1. From Spreadsheet Lists to Data Models

A data model is a formal description of the data a system stores, the meaning of that data, and the relationships among data elements. In supply-chain management, a model might represent the following entities:

• Product: an item that can be purchased, stored, sold, or transported.
• Supplier: an organization providing products or services.
• Location: a warehouse, plant, store, port, or customer site.
• Purchase order: a commercial request made to a supplier.
• Purchase-order line: a particular product and quantity on an order.
• Shipment: a movement of goods.
• Inventory snapshot: stock measured at a particular location and time.

The distinction between an entity and an event is important. A product exists as a relatively stable object, while a shipment or inventory movement records something that happened. Entity tables usually contain descriptive attributes; event tables usually contain dates, quantities, statuses, and references to other entities.

A structured spreadsheet table should normally satisfy four conditions:

1. Each column has one clear meaning.
2. Each row represents one type of object or event.
3. Each cell contains one atomic value rather than a list.
4. The table has a stable identifier or a clearly documented key.

For example, the following is poorly structured:

The row contains multiple products and multiple contact values. It is difficult to filter, validate, or join reliably. A more useful design separates suppliers and products, then records their supply relationship in an associative table.

The third table resolves a many-to-many relationship: one supplier can provide many products, and one product can be provided by many suppliers.

2. Relational Structure and Keys

A relational model organizes data into relations, commonly implemented as tables. A relation has attributes (columns) and tuples (rows). In practical spreadsheet work, the formal relational terminology helps users reason about duplication, integrity, and joins.

Primary keys

A primary key uniquely identifies each row in a table. A product code such as P-101 may be a natural key if it is controlled, stable, and unique. An automatically generated ProductID is a surrogate key. Neither approach is universally superior.

A good key should be:

• unique within its table;
• non-null;
• stable over the relevant life of the record;
• sufficiently simple for validation and joining; and
• governed by an explicit ownership rule.

A composite primary key uses more than one column. In a purchase-order-line table, (PurchaseOrderID, LineNumber) may identify a row if line numbers are unique only within an order.

Foreign keys

A foreign key is a column whose values refer to keys in another table. For example, SupplierID in a purchase-order table refers to SupplierID in the supplier table. Foreign keys establish relationships and support referential integrity.

Referential integrity requires that a non-null foreign-key value correspond to an existing parent record. If a shipment contains SupplierID S-999 but S-999 is absent from the supplier table, the model contains an orphan record. Spreadsheet tools may not automatically prevent this, so validation rules, lookup checks, and exception reports are necessary.

Relationship cardinality

Cardinality describes how many records in one table may relate to records in another.

A relationship may also be optional. A purchase order may have zero or many shipments; a shipment normally belongs to one purchase order, but emergency or spot-buy shipments may require a different business rule. Such rules should be documented rather than inferred from the spreadsheet layout.

Figure: An associative table resolves the many-to-many relationship between suppliers and products.

3. Normalization as a Design Discipline

Normalization is a set of principles for reducing redundancy and preventing update anomalies. It does not mean that every analytical model must contain the maximum possible number of tables. Rather, normalization helps the designer understand dependencies before deciding whether controlled denormalization is useful for reporting performance or usability.

First normal form

A table is in first normal form (1NF) when each field contains an atomic value and repeating groups have been removed. A cell containing “P-101; P-102; P-107” violates the practical intent of 1NF because it stores multiple product values in one field.

Second normal form

A table is in second normal form (2NF) when it is in 1NF and every non-key attribute depends on the whole primary key, not merely part of a composite key. Suppose a purchase-order-line table has key (OrderID, LineNumber), but includes SupplierName. SupplierName depends on OrderID, not on the complete line key. It should normally be stored in the order or supplier table.

Third normal form

A table is in third normal form (3NF) when it is in 2NF and non-key attributes do not depend transitively on another non-key attribute. If a supplier table stores SupplierID, SupplierName, and PaymentTermsDescription, and the payment-terms description is determined by PaymentTermsCode, then PaymentTermsDescription may belong in a payment-terms reference table.

Normalization prevents three classic anomalies:

• Insert anomaly: a new supplier cannot be recorded until an order exists.
• Update anomaly: a supplier address must be changed in many duplicated rows.
• Delete anomaly: deleting the last order accidentally removes the only record of a supplier.

Normalization and analytical models

Operational systems often favor normalized structures because transactions must be inserted and updated safely. Analytical models frequently use a star schema. A fact table stores measurable events, such as shipped quantity or sales value. Dimension tables describe the facts, such as product, supplier, date, and location.

A star schema may intentionally repeat descriptive values through dimension keys or flattened attributes to make analysis easier. This is controlled denormalization, not careless duplication. The decision should reflect query performance, user comprehension, refresh processes, and governance requirements.

4. Facts, Dimensions, Grain, and Time

The grain of a table is the precise meaning of one row. Examples include “one row per purchase-order line,” “one row per shipment event,” or “one row per product-location-day inventory snapshot.” Grain must be declared before calculations are created.

Consider a table containing ProductID, LocationID, SnapshotDate, OnHandQuantity, and InventoryValue. Its grain is one product at one location on one date. If the table instead contains one row per daily inventory transaction, summing OnHandQuantity may be meaningless because stock balances are not additive across transactions.

Measures can be classified as:

• Additive: can be summed across all relevant dimensions, such as units shipped.
• Semi-additive: can be summed across some dimensions but not time, such as end-of-day inventory balance.
• Non-additive: should not be summed, such as percentages, rates, and unit prices.

This classification is essential for pivot tables. A monthly inventory balance is usually the last valid balance or an average balance, not the sum of daily balances. A service percentage should be recalculated from underlying counts rather than averaged across warehouses unless equal weighting is explicitly intended.

Dates deserve particular attention. A shipment may have order date, promised date, dispatch date, and delivery date. These are different business roles of time. A model should identify which date drives each analysis and should not silently substitute dispatch date for delivery date.

5. Building a Spreadsheet Data Model

A practical workbook can contain separate tables for:

1. Reference data: products, suppliers, locations, currencies, units of measure.
2. Transactions: purchase orders, receipts, shipments, returns, inventory movements.
3. Calendar: dates, weeks, months, fiscal periods, holidays.
4. Measures or outputs: pivot tables, dashboards, exception lists, and approved KPIs.

Use table features rather than unbounded cell ranges. Assign meaningful table names, such as tblProducts, tblOrders, and tblShipments. Avoid merged cells inside data tables, blank rows, decorative subtotals, and mixed data types in a column.

Data-quality controls

Useful controls include:

• uniqueness tests for primary keys;
• lookup tests for foreign keys;
• permitted-value lists for status fields;
• date-order tests, such as delivery date not preceding dispatch date;
• quantity and price rules that reject inappropriate negative values;
• missing-value reports;
• duplicate detection based on business keys; and
• reconciliation totals between source and output tables.

A formula such as [[MATH=COUNTIF(tblProducts[ProductID],[@ProductID])]] is often used in spreadsheets to test whether an identifier exists, although the exact syntax varies by software. The underlying rule is more important than the function: every foreign key should be checked against its parent key.

6. Pivot Tables and Relational Analysis

A pivot table summarizes records by dimensions and measures. It is not a substitute for a correct data model. Pivot results are only as reliable as the table grain, key integrity, and aggregation rule.

Suppose a shipment table has the following records:

A pivot with SupplierName in rows and Units in values calculates total units by supplier. A pivot with LocationID in rows and FreightCost in values calculates freight cost by location. If ProductName is added through a one-to-many join that duplicates shipment rows, totals may be overstated. This is a common failure when users flatten tables without preserving grain.

Calculated fields and measures

A calculated field derives a value from fields in the same record or from an explicitly defined aggregation. For a shipment record, freight cost per unit is:

where C_u is freight cost per unit, F is freight cost in currency units, and Q is shipped quantity in units. The formula applies when Q is positive and freight cost is allocated entirely to that shipment. If freight is recorded at the route or container level, an allocation rule is required before calculating shipment-level cost.

For aggregated data, the correct weighted freight cost per unit is:

where i indexes shipments included in the group. Averaging individual freight costs per unit gives each shipment equal weight and may produce a different, inappropriate result.

Worked example

Three shipments have freight costs of 500, 300, and 720 currency units and quantities of 100, 80, and 120 units. Total freight is:

500 + 300 + 720 = 1,520 currency units.

Total quantity is:

100 + 80 + 120 = 300 units.

Therefore:

C_u,total = 1,520 ÷ 300 = 5.0667 currency units per unit, approximately 5.07.

The simple average of the three shipment-level rates would be (5 + 3.75 + 6) ÷ 3 = 4.9167, which answers a different question and understates the quantity-weighted rate.

7. Reusable Semantic Definitions

A semantic definition specifies what a metric means, how it is calculated, its grain, filters, exclusions, owner, and refresh frequency. Without such definitions, different analysts may publish measures with identical names but incompatible meanings.

For on-time delivery, if each eligible order is counted once:

N_on-time is the number of eligible orders delivered on or before the promised date, and N_eligible is the number of eligible delivered orders. The denominator must be nonzero, and the organization must state whether cancelled, partially delivered, or late-but-accepted orders are included.

For fill rate measured by units:

This differs from order fill rate, which counts orders completely filled. The two metrics should not be blended.

A semantic layer may be implemented through a controlled metric dictionary, data catalog, governed pivot measures, or a business-intelligence model. The technology is secondary to agreement about meaning and lineage.

8. Illustrative Case: Meridian Components

Background

Meridian Components is a fictional regional distributor serving industrial customers. Its procurement workbook contains supplier, product, purchase-order, receipt, and shipment information. Managers report that supplier performance varies widely between monthly reports.

Problem

An analyst discovers that supplier names are manually typed in transaction sheets, products are sometimes identified by description rather than code, and a shipment may appear several times after being joined to purchase-order lines. One report calculates on-time delivery by order; another calculates it by shipment line. Inventory is summed across daily snapshots, producing implausibly large stock values.

Analysis

The team documents table grain:

• Supplier table: one row per supplier.
• Product table: one row per product.
• Purchase-order line table: one row per order line.
• Receipt table: one row per receipt line.
• Shipment table: one row per shipment event.
• Inventory table: one row per product-location-day.

They establish SupplierID and ProductID as controlled keys. PurchaseOrderID and LineNumber form the order-line key. Foreign-key exception reports identify 14 obsolete product codes and 6 unmatched suppliers.

Approach and solution

The workbook is redesigned into structured tables. Supplier and product descriptions are removed from transaction entry fields and retrieved through validated identifiers. A calendar table supplies fiscal periods. Pivot tables use additive units shipped and freight cost, while inventory analysis uses end-of-period balances rather than sums of daily balances.

The organization approves a metric dictionary. On-time delivery is defined at the purchase-order-line level, using the confirmed promised date and excluding cancelled lines. Fill rate is defined by units. Each metric includes an owner, source tables, refresh schedule, and exception treatment.

Outcome

The first monthly report shows a lower but more credible on-time rate because duplicate shipment rows are removed. Freight cost per unit increases slightly after correct quantity weighting. Inventory values become operationally plausible after the model stops summing balances across time. Managers can now trace each KPI to source records and explain differences between reports.

Learning points

1. A pivot table cannot correct an incorrect grain.
2. Keys are governance mechanisms, not merely technical labels.
3. Metric definitions must specify denominators and exclusions.
4. Normalization reduces contradictory versions of descriptive data.
5. Controlled denormalization may improve analysis when its effects are documented.

9. Challenges, Limitations, and Governance

Spreadsheet models remain vulnerable to accidental overwriting, inconsistent formulas, hidden filters, broken links, and uncontrolled copies. Relational design reduces these risks but does not eliminate them. A well-designed model can still produce poor decisions if source data are incomplete, delayed, biased, or measured inconsistently.

Governance should address ownership, access, version control, retention, and auditability. Supplier performance data may affect contract negotiations and should be protected from unauthorized alteration. Personal information in contact tables should be minimized and handled according to applicable privacy obligations. Access should follow least privilege: users receive only the permissions required for their roles.

Metrics can also create behavioral risks. If buyers are evaluated only on purchase price, they may select suppliers with poor quality or unreliable delivery. If warehouses are evaluated only on inventory reduction, they may create stockout risk. A metric is therefore not neutral; it shapes organizational attention and incentives.

AI-enabled spreadsheet assistants can help identify duplicates, suggest relationships, generate formulas, and summarize anomalies. However, generated relationships and formulas require human validation. AI may infer a relationship from similar names even when identifiers are not equivalent, and it may conceal assumptions in a calculated metric. Governance should require source traceability, testing, access controls, and explicit review of automated transformations.

10. Best Practices for Graduate-Level Practice

1. Declare the grain before building formulas or pivots.
2. Use stable, governed keys rather than names as join fields.
3. Separate entities, events, and reference data.
4. Resolve many-to-many relationships with associative tables.
5. Normalize operational data enough to prevent update anomalies.
6. Classify measures as additive, semi-additive, or non-additive.
7. Use weighted calculations when aggregating rates or unit costs.
8. Validate foreign keys and reconcile source-to-output totals.
9. Maintain a metric dictionary with definitions and owners.
10. Record assumptions, exclusions, date roles, and refresh schedules.
11. Treat controlled denormalization as a documented design choice.
12. Test models with edge cases, including missing keys, zero denominators, duplicates, cancellations, and partial deliveries.

Chapter Summary

Relational thinking transforms a spreadsheet from a collection of lists into a structured representation of supply-chain entities and events. Tables should have a clear grain, atomic values, stable keys, and documented relationships. Primary keys identify records; foreign keys connect them; associative tables resolve many-to-many relationships.

Normalization concepts—especially 1NF, 2NF, and 3NF—help prevent insertion, update, and deletion anomalies. Analytical models may use fact and dimension tables, with controlled denormalization where it improves reporting. Pivot tables are powerful only when the underlying grain and aggregation rules are correct. Additive, semi-additive, and non-additive measures must be treated differently, particularly for inventory balances, rates, and percentages.

Reusable semantic definitions make metrics consistent across analysts and systems. Definitions should specify formulas, denominators, date roles, exclusions, grain, ownership, and lineage. Finally, data modeling is also a governance activity: keys, definitions, access controls, and audit trails influence operational behavior and managerial trust.

Key Terms

Atomic value: A single indivisible value in a field for the intended model.

Associative table: A table that resolves a many-to-many relationship using foreign keys.

Cardinality: The number of records that may relate across two tables.

Calculated field: A field derived from other fields through a defined expression.

Composite key: A key formed from two or more attributes.

Dimension table: A descriptive table used to classify and filter fact records.

Fact table: A table containing measurable events or business processes.

Foreign key: An attribute referring to a key in another table.

Grain: The precise meaning represented by one row.

Key: An attribute or set of attributes used to identify or relate records.

Many-to-many relationship: A relationship in which multiple records on each side may be associated.

Normalization: A design discipline that reduces redundancy and update anomalies.

Primary key: The unique, non-null identifier for a table row.

Referential integrity: The requirement that valid foreign-key values correspond to parent records.

Relational model: A representation of data as related tables of attributes and records.

Semantic definition: A governed statement of a metric’s meaning, calculation, scope, and assumptions.

Semi-additive measure: A measure that can be summed across some dimensions but not all, commonly time.

Star schema: An analytical model with a central fact table and surrounding dimension tables.

Surrogate key: A system-generated identifier without direct business meaning.

Transitive dependency: A dependency in which a non-key attribute depends on another non-key attribute.

Review Questions

Short Answer Questions

1. What is the difference between a primary key and a foreign key?
2. Why must the grain of a table be declared before creating a pivot table?
3. Give one example of an additive measure and one example of a semi-additive measure.
4. What problem does an associative table solve?
5. State one purpose of a semantic metric definition.

Long Answer Questions

1. Explain how normalization prevents insertion, update, and deletion anomalies in a supplier-management workbook.
2. Compare an operational normalized model with an analytical star schema.
3. Discuss why averaging shipment-level freight cost per unit may produce a different answer from calculating total freight divided by total quantity.
4. Explain how keys, validation, and referential integrity improve supply-chain reporting.
5. Evaluate the governance risks of using AI-generated formulas and inferred spreadsheet relationships.

Analytical / Application Questions

1. Design tables and relationships for a process involving suppliers, purchase orders, receipts, and supplier invoices. State the grain and primary key of each table.
2. A warehouse records end-of-day inventory for 30 days. Explain why summing the daily balances is inappropriate for month-end inventory, and propose a suitable aggregation rule.
3. A supplier has 90 on-time order lines out of 120 eligible order lines. Another supplier has 9 on-time lines out of 10. Calculate both on-time percentages and explain why ranking should consider volume and business context rather than percentage alone.

Multiple Choice Questions with Answers

Question 1

Which characteristic is required of a primary key?

A. It must contain descriptive text

B. It must uniquely identify each row

C. It must be repeated in every related table

D. It must always be generated automatically

Correct Answer: B. It must uniquely identify each row

Question 2

Which table best resolves the many-to-many relationship between suppliers and products?

A. A table containing SupplierID and ProductID

B. A table containing only supplier names

C. A table containing only product descriptions

D. A table containing monthly totals only

Correct Answer: A. A table containing SupplierID and ProductID

Question 3

What is the grain of an inventory table with one record for each product, location, and day?

A. One supplier per month

B. One product per supplier

C. One product-location-day combination

D. One warehouse per product category

Correct Answer: C. One product-location-day combination

Question 4

Which measure is generally semi-additive because it should not normally be summed across time?

A. Units shipped

B. Freight cost

C. Purchase-order count

D. End-of-day inventory balance

Correct Answer: D. End-of-day inventory balance

Question 5

Which normal-form principle requires values in fields to be atomic rather than lists?

A. First normal form

B. Second normal form

C. Third normal form

D. Boyce-Codd normal form only

Correct Answer: A. First normal form

Question 6

Total freight cost is 1,520 and total shipped quantity is 300 units. What is the quantity-weighted freight cost per unit?

A. 4.92

B. 5.07

C. 6.00

D. 456,000

Correct Answer: B. 5.07

Question 7

Which practice most directly helps prevent orphan transaction records?

A. Using merged cells

B. Sorting rows alphabetically

C. Validating foreign keys against parent keys

D. Adding decorative subtotals

Correct Answer: C. Validating foreign keys against parent keys

Question 8

If 90 of 120 eligible order lines are on time, the on-time delivery percentage is:

A. 25%

B. 60%

C. 75%

D. 90%

Correct Answer: C. 75%

Question 9

Why can joining a shipment table to a one-to-many detail table inflate totals?

A. The join may duplicate shipment rows

B. The join automatically converts units to currency

C. The join removes all primary keys

D. The join prevents filtering by date

Correct Answer: A. The join may duplicate shipment rows

Question 10

Which item should a semantic metric definition specify?

A. Only the metric’s display color

B. Only the analyst’s name

C. The formula, scope, exclusions, and denominator

D. A guarantee that the metric never changes

Correct Answer: C. The formula, scope, exclusions, and denominator