Before counting, joining, or aggregating records, state what one row represents. That definition is the table’s grain. A row could represent a customer, a complete order, one product line, or one return event. Each grain supports different questions.
An order has 1 total and 3 products. Is that 1 record or 3? The answer depends on what 1 row is meant to represent. If a row represents 1 order, the order appears 1 time. If a row represents 1 order line, the same order has 3 rows. Both designs can be useful.
Confusing them can change a count or multiply a total. A relational table organizes records as rows and fields as columns under a defined structure. The row has a grain: the specific kind of thing 1 row represents. It might be 1 customer, 1 order, 1 line within an order, or 1 return event. State that grain before joining or counting records.
Return to order O101. Its total is $120. It contains 3 product lines. Suppose a query joins the order row to all 3 line rows and then sums the order total. The $120 value now appears 1 time for each line. A naive sum reports $360. The order did not get more expensive;
the join changed the number of rows carrying the repeated total. To answer the business question, aggregate at the intended grain or handle the repeated order-level amount explicitly. Tables also need identifiers. A primary key identifies a row uniquely under the table’s rules. An orders table might use order_id.
If 1 order contains several lines, the order ID alone cannot uniquely identify every line. A composite key can use order_id together with line_number. A primary key is a database constraint, and its exact behavior is part of the system’s definition. Uber Engineering's January 15, 2016 account describes its operating trip datastore using
a cell identified by a row key, column name and revision key. A new version has a higher revision key. Its underlying MySQL table also has a separate primary key for each stored cell. That distinction matters: a stored row can represent a version of part of an entity. Counting those rows would answer
a different question from counting trips. State the unit and the identifier before using either count. A foreign key refers to a key in another table. If an order row contains customer_id, that value can point to the corresponding customer row. It helps establish a defined relationship. A name is often a weaker key: 2 customers can share 1,
and a person’s name can change. Even a valid identifier can point to the wrong customer if an upstream process assigned it incorrectly. The constraint checks a relationship under the database’s rules; it does not prove the business event was recorded correctly. Separate tables can represent concepts that change independently. Customer contact details can live with the customer, rather than being copied into each order.
Product details can live with products. Order lines can preserve item-level quantities, and returns can be stored as separate events. This organization reduces some duplication and update problems. It is not a reason to split every field into its own table; the design should match how the business records and uses information. Here is the checkpoint.
Why should a dataset say “1 row equals 1 customer”? Pause and decide. Because counts, joins, labels, and evaluation splits depend on the grain. If a customer has several rows, treating each row as a different customer can overstate the number of independent people. Try a different case.
Repair job R42 has 4 parts. Part number 7 appears in many jobs. What pair could identify 1 part row if a part number appears only 1 time within each job? Pause and answer. The job identifier plus the part number can form a composite key under that stated rule. A real system must define and enforce the rule.
Return to the opening order. 1 order row holds its total, 3 line rows hold its products, and defined keys connect them. You can now explain which unit a count measures and prevent a repeated order total becoming extra revenue. Next we will use queries and joins deliberately, including when a relationship is missing.
Main Point
Before counting, joining, or aggregating records, state what one row represents. That definition is the table’s grain. A row could represent a customer, a complete order, one product line, or one return event. Each grain supports different questions.
Key Points
State row grain before counting, joining or summing.
Use keys whose full scope identifies the intended record.
Keep independently changing facts in appropriate related tables.
1. Worked example: interpret the opening record
Suppose order O101 has a total of 120 dollars and contains three product lines. A join from the order record to its three lines repeats the order-level total on each resulting row. Summing the repeated values gives 360 dollars. The order did not become more expensive; the query combined a one-order fact with three line records. Calculate at the intended grain, or explicitly handle the repeated value.
A primary key identifies a table row uniquely under the table’s rules. An order can use an order identifier. Individual lines can use the order identifier together with a line number. A foreign key refers to a key in another table, such as an order’s customer identifier referring to a customer row. Constraints can catch some invalid references, but a valid identifier can still have been assigned to the wrong business event upstream.
Names are often poor identifiers because people can share names or change them. Identifiers also need a defined source: two systems may use the same visible code for different records. Preserve the source namespace and state what combination of fields identifies one row.
Separating customer, order, product, line, and return facts can reduce duplication and some update anomalies. The design should match how information changes and how questions are asked. It is not enough to draw boxes and arrows; say what one row means and which rule connects the rows.
2. Check
For every table, write one sentence beginning “One row represents…” Then inspect each join: what is the grain before, what is it after, and which values are repeated or dropped?
3. A dated example
Uber Engineering's January 15, 2016 account describes its operating trip datastore using a cell identified by a row key, column name and revision key. A new version has a higher revision key. Its underlying MySQL table also has a separate primary key for each stored cell. That distinction matters: a stored row can represent a version of part of an entity. Counting those rows would answer a different question from counting trips. State the unit and the identifier before using either count.
4. Bringing the lesson together
The opening order has one order row and three line rows under two stated grains. You can count orders, inspect lines and update a customer without confusing those units. Before using the result downstream, explain the key relationship and every value the join repeats.
Learning Checks
Attempt each answer before expanding the reasoning.
M13-L02-Q01
An order total of 120 USD joins to three order-line records. Why can a naive sum report 360 USD?
Show reasoning
The order-level amount has been repeated across three rows at the finer order-line grain. The query must aggregate at order grain or otherwise handle the repeated value.
M13-L02-Q02
What does a foreign key establish, and what does it not establish?
Show reasoning
It references a key in another table under database rules; it does not prove the upstream business event assigned the correct relationship.
M13-L02-Q03
A repair job R42 has four parts. Part number 7 appears in many jobs but at most once within one job.
Under the stated rule, what pair identifies a part row in repair job R42?
Show reasoning
Job identifier plus part number.
M13-L02-Q05
Two systems both issue O101. What source information is needed?
Show reasoning
Preserve source namespaces and verify the relationship/identifier rule.
M13-L02-Q06
Why state one row’s grain? What are the Orders and Order Lines grains in the example?
Show reasoning
Counts, joins, labels and splits depend on grain. Orders: one order per row; Order Lines: one product line per row.
Sources
PostgreSQL Global Development Group, PostgreSQL 18 documentation, “Constraints,” sections 5.5.4 “Primary Keys” and 5.5.5 “Foreign Keys”: https://www.postgresql.org/docs/18/ddl-constraints.html (retrieved 2026-10-29). This is one implementation example, not a claim that every database has identical behavior.
Jakob Holdgaard Thomsen, Uber Engineering, “The Architecture of Schemaless, Uber Engineering’s Trip Datastore Using MySQL”, published 2016-01-15; event/context: Architecture reported January 2016; datastore operating since October 2014. https://www.uber.com/us/en/blog/schemaless-part-two-architecture/ (retrieved 2026-10-29). Company account of its 2016 architecture; illustrates entity/version/key distinction, not a recommendation to reproduce it.
Use the supplied fictional cases and templates for practice.
Practice
Write your answer, compare it with the model answer and decide what to revisit. These written checkpoints are self-assessed.
An order total of 120 USD joins to three order-line records. Why can a naive sum report 360 USD?
Show The Model Answer
The order-level amount has been repeated across three rows at the finer order-line grain. The query must aggregate at order grain or otherwise handle the repeated value.
Compare your reasoning with the model answer. These written checks are self-assessed.
What does a foreign key establish, and what does it not establish?
Show The Model Answer
It references a key in another table under database rules; it does not prove the upstream business event assigned the correct relationship.
Compare your reasoning with the model answer. These written checks are self-assessed.
Under the stated rule, what pair identifies a part row in repair job R42?
Show The Model Answer
Job identifier plus part number.
Compare your reasoning with the model answer. These written checks are self-assessed.
Two systems both issue O101. What source information is needed?
Show The Model Answer
Preserve source namespaces and verify the relationship/identifier rule.
Compare your reasoning with the model answer. These written checks are self-assessed.
Why state one row’s grain? What are the Orders and Order Lines grains in the example?
Show The Model Answer
Counts, joins, labels and splits depend on grain. Orders: one order per row; Order Lines: one product line per row.
Compare your reasoning with the model answer. These written checks are self-assessed.