Chapter 1: SQL and the Shop Database
What You Will Learn
- Classify information for Online Shop Order Management into four distinct groups.
- State a business rule for customers, products, orders, and order items.
- Explain what a SQL query requests and what the database server decides.
- Distinguish PostgreSQL from
psql and Visual Studio Code.
- Explain why linked, transactional shop data fits a relational database.
Prerequisites
No additional prerequisites beyond those listed in About This Book.
Concept Overview
Online Shop Order Management needs to record who buys, what is available, and what was purchased. A relational database organizes these facts into tables, with each row representing one instance, such as a customer or an order. An order can refer to a customer by storing the customer’s identifier. For this project, the four information groups are customers, products, orders, and order items.
Separating those groups protects purchase history. A customer can place multiple orders without having contact details copied into every order. Each order item records a product, a quantity, and the price charged at purchase. That recorded price determines what the customer paid, even if the product’s listed price changes. When an order is placed, the order and all its items must be saved together or none should be saved.
When I check shop order reports after a catalog price change, totals based on current prices expose a specific error: they misstate earlier purchases. Purchase-time prices prevent it.
SQL is declarative: a query requests a result, such as orders belonging to a customer, without prescribing the retrieval steps. PostgreSQL stores the data, chooses those steps, and processes the query. psql sends commands to PostgreSQL and displays responses; Visual Studio Code edits files, including SQL files. Neither client tool is the database server.
SQL Server comparison: SQL Server is another relational database server. Its client tools differ from psql. Later examples will label dialect-specific syntax rather than assume that commands run unchanged on both servers.
Linked records and orders that must be saved as complete units make a relational model the right starting point here. A document model can be justified when the main unit of storage is a self-contained document with variable structure and few relationships requiring database-enforced consistency. That is not the primary shape of this shop’s order data.
Step-by-Step Implementation
This chapter defines requirements, not tables. Do not run SQL yet. Chapter 2 establishes the PostgreSQL connection in the Visual Studio Code integrated terminal using psql. Chapter 4 supplies the schema, which defines the tables and their rules, and deterministic seed data, which produces the same starting records each time it is loaded. The SQL expressions below illustrate the price distinction without depending on shop tables. Save and run them only after completing the connection setup in Chapter 2.
Step 1: Separate identity from purchasing activity
Identify facts that describe a customer independently of any purchase, such as a name and contact information. Do the same for a product: its name and current listed price describe it independently of any order.
Now distinguish an order from its contents. The order identifies the customer’s purchase. Each order item records one product in that purchase, along with its quantity and purchase-time price.
Keep purchase-time prices with order items.
This separation matters when a customer places another order or a product price changes. Avoid using a product’s current price as the sole record of an earlier purchase.
Step 2: Assign one basic rule to each group
Use rules to test whether each group has a clear purpose:
- Customer: Contact information belongs to a specific customer.
- Product: A product’s listed price cannot be negative.
- Order: An order belongs to one customer.
- Order item: An item belongs to one order, refers to one product, and has a positive quantity.
Write rules before creating tables.
These are requirements, not enforced constraints. A constraint is a rule the database checks when data changes. Later chapters will add appropriate constraints to the schema and test that they reject invalid data. I include zero quantity in the shop’s invalid-data test cases because “positive” excludes zero.
Step 3: Classify a purchase
Complete this task before reading the Full Example:
- Assign Maya Chen’s contact information, a ceramic mug’s name and listed price, Maya’s purchase, and two mugs bought in that purchase at $16 each to the four groups.
- Write one business rule for each group.
- State which price remains $16 when the mug’s listed price rises to $18.
Your result should contain four groups, not one large purchase record. Use the Full Example to check your classification.
The following PostgreSQL expression shows the calculation a later order report must make:
It returns 32, the amount charged for two mugs at $16 each. The expression uses fixed values, not shop tables. An order report must eventually read quantity and purchase-time price from order items instead of embedding those values in SQL.
Full Example
Suppose Maya Chen buys two ceramic mugs. At checkout, each mug costs $16. The product’s listed price later changes to $18.
| Proposed group | Information it owns in this example | Business rule |
| Customers | Maya’s identity and contact information | Contact information belongs to Maya, not to an individual purchase. |
| Products | The ceramic mug and its current listed price of $18 | The listed price cannot be negative. |
| Orders | Maya’s purchase | The order belongs to Maya. |
| Order items | Two ceramic mugs purchased at $16 each | The item belongs to the order, refers to the mug, and has a positive quantity. |
The expected result is four groups with separate responsibilities. The $18 listed price describes the product now; the $16 purchase-time price remains the amount charged per mug. Changing the catalog must not rewrite the facts of a completed purchase.
This PostgreSQL example combines the earlier calculation with the current listed price. It requires a PostgreSQL connection but no shop schema:
The result is one row with charged_total equal to 32 and current_listed_price equal to 18. Keeping both values visible prevents a catalog update from being mistaken for a change to Maya’s purchase. These literals demonstrate the requirement; they are not a substitute for stored order items.
This is a requirements example, not a runnable shop schema. Column types and keys, which identify rows and connect related rows, have not been selected yet. Those decisions follow after the project has a working PostgreSQL environment and a detailed relationship model.
Common Mistakes
- Putting every fact into one order record. This copies customer and product information across purchases. Separate facts by responsibility before choosing columns.
- Using the current product price as purchase history. A catalog update changes the apparent value of old orders. Record the purchase-time price with each order item.
- Treating Visual Studio Code or
psql as the database. These tools edit files or send commands; PostgreSQL stores and processes the data. Keep that distinction clear when diagnosing connection problems.
- Assuming a business rule is already enforced. A written rule does not reject invalid data. Track each rule until it is implemented and tested.
Correctness comes first for this purchase: the order and its items must be saved together, and its $16 item price must survive a catalog change. Copying Maya’s contact details into every order creates extra values to keep consistent.
Do not duplicate those facts to make queries appear faster. Defer index decisions as well. An index is an additional structure that can help the database find rows, but it takes space and adds work when data changes. Choose indexes after you know which queries the shop runs and can measure how they perform.
Summary
Online Shop Order Management starts with four information groups: customers, products, orders, and order items. Their relationships and purchase-time rules make a relational database an appropriate starting point. PostgreSQL stores and processes the data; psql and Visual Studio Code are tools for working with it. Keep current product information separate from the facts of a completed purchase.
Key Terms
- Relational database: Data organized into related tables.
- SQL: A declarative language for requesting and changing relational data.
- Database server: Software that stores data and processes queries, such as PostgreSQL.
- Client tool: Software that sends commands to a database server, such as
psql.
- Business rule: A requirement that defines valid data or relationships.
- Purchase-time price: The price recorded for an order item when the purchase occurs.
Mini Quiz
- A mug’s listed price changes from $16 to $18 after Maya buys two. Which value must the order item retain?
A. $16 per mug
B. $18 per mug
C. The average of the two prices
D. No price, because the product stores it
- A developer writes a query that states which rows to return but does not specify how to retrieve them. What chooses the retrieval steps?
A. Visual Studio Code
B. psql
C. The PostgreSQL server
D. The customer record
- Which group should own the quantity of mugs in Maya’s purchase?
A. Customers
B. Products
C. Orders
D. Order items
- A team writes “product prices cannot be negative” in its requirements document. What is true at this stage of the project?
A. PostgreSQL already rejects negative prices.
B. The rule is documented but not yet enforced by the database.
C. psql enforces the rule when displaying products.
D. The rule belongs on each customer record.
Answers are provided in the Answer Key at the end of the book.
Activity
- Create a four-row classification table for Online Shop Order Management. Label the rows customers, products, orders, and order items.
- Assign each fact to one group: Maya Chen’s contact information, the ceramic mug and its listed price, Maya’s purchase, and two mugs bought in that purchase at $16 each.
- Write one business rule for each group. State which group owns the quantity and which group owns the purchase-time price.
- Suppose the mug’s listed price rises to $18. Record the listed price and the purchase-time price separately, then check your classification against the Full Example.
The expected result is four groups with distinct responsibilities and one rule each. The product’s listed price is $18; the order item’s purchase-time price remains $16 per mug.