Designing your database

TL;DR#

Today we opened the database project and started designing databases on paper: entities and attributes, primary keys, foreign keys, and the table in the middle that a many-to-many relationship needs. The habit worth keeping: store every fact in one place, and read each relationship aloud in both directions before you trust it.

Where a fact belongs#

Store each fact once. A sales report that repeats the customer's phone number on every line has to change on 7595 rows when the number changes, and a missed row leaves two numbers with nothing to say which is right. The fix is a customers table that holds the phone once, and orders that point to it.

Entity or attribute. An entity is a kind of thing the business keeps many of, such as customers or orders. It becomes a table, and each customer a row. An attribute is one fact about one of them, such as a customer's city, and it becomes a column. When you are unsure, ask whether the business keeps facts of its own about it. A city with its own population and mayor is an entity; a city that only completes an address is an attribute.

A cell holds a single value. An order holds several products, each with its own quantity and discount, so neither the orders row nor the products row can hold them. That is why the order lines get a table of their own.

Why the price is kept on the order line#

Both tables have a unit price, and they hold two different facts:

• products.UnitPrice is today's list price. It changes, and a new order starts from it.
• order_details.UnitPrice is the price charged on that order. Once written, it never changes.

If the price lived only in products, raising it tomorrow would change what every past order was worth, and your revenue reports would stop matching the invoices customers hold. In Northwind, Pavlova lists at $17.45 today; it was charged $13.90 on 13 order lines and $17.45 on 7891.

The discount sits on the line too, because two lines of one order can carry different discounts. The amount on an invoice line, quantity times unit price times one minus the discount, is computed when you need it, so no table has a column for it.

The database project meets the same idea twice: scenario B keeps a closing price for every day and never writes over an earlier one, and scenario C keeps on each order line the price paid that day.

Keys#

• A primary key names exactly one row. No two rows share it, and it is never empty. Give every table one.
• A surrogate key is an ID you add when you design the table, such as ProductID. A value the business already uses, such as a name, an email or a stock ticker, can change: Facebook's FB became META.
• When a pair of columns must not repeat, such as a user and a post in a table of likes, you have two sound designs. Make the pair the primary key, a compound key; or add a surrogate like_id and declare the pair UNIQUE. Either way, nobody likes one post twice. Your README explains which you chose and why.
• A table has one primary key. Another column that must also stay unique, such as a student's email beside the student ID, is declared UNIQUE.
• An ID that counts up tells a competitor how many orders you take. Place one order this week and one next week, subtract the two numbers, and you have an estimate. A UUID gives nothing away.

Foreign keys#

• A foreign key holds another table's primary key, on the many side. An order holds its customer's ID, and the database refuses an order whose customer is not in customers.
• A table can hold two foreign keys to the same table. A flight has one for the airport it leaves and one for the airport it lands at, both pointing to airports.
• A foreign key can point into its own table. employees.ReportsTo holds another employee's ID. It is empty for one employee, the Vice President, Sales, who reports to nobody.

Relationships#

• Read a relationship aloud in both directions, and state your assumption. "A customer places zero or more orders; an order has exactly one customer" is one to many. Another business could assume otherwise, so your project's README states the assumptions your design rests on.
• One to one is usually one table. Split it only when some rows lack the facts (a loyalty card only some customers hold), when fewer people may read them (salaries), or when they change at a different pace.
• Many to many needs a table in the middle. An order holds many products and a product is on many orders, so order_details has one row for each product on each order. Its two key columns are each a foreign key, one to orders and one to products, and together they are its primary key. Students and courses, employees and projects, and playlists and songs work the same way.

Let your agent question your plan first#

An agent asked to build something usually agrees with you and starts building. Before it writes anything, ask it to interview you until the plan is clear. This prompt works in any chat, Copilot, ChatGPT or Claude:

Before you build anything, interview me about this plan. Ask a few questions at a time, numbered, each with the answer you would recommend, and wait for my answers before the next round. Keep going until nothing is left assumed, then summarize what we agreed. Do not start building until I confirm the summary.

It is a short version of the grill-me skill shown in class.

Review from last week#

• A POST carries its data in the body of the request, so it stays out of the address bar and the browser's history. What keeps it private on the way is HTTPS, which protects the address and the body alike.
• Everything read back from a CSV file is a string. "49831" * 2 repeats the text; int("49831") * 2 gives 99662.
• A function that only prints gives back None. The Print vs Return analogy: printing is saying the number of steps out loud; returning is writing it down so you can use it later.

Public links#

• Session page
• Topic slides: Databases and SQL
• The database project
• oim-shop.zhili.dev, Northwind's sales system

Before next session#

1. The database project: read the whole page, Part A and Part B. Part A is due Sunday 11/08.
2. The Midterm Feedback Survey, if you did not finish it in class. It is anonymous and is due Sunday 11/01.
3. Mini Project 2 revision, if you want the points back: fix everything in my comment, push, and reply under the comment on Canvas by Sunday 11/01. A fix earns back half the points.
4. Your personal website has to open at https://<your-github-username>.github.io. If it does not, rename the repository to your GitHub username followed by .github.io, as the 10/14 notes describe.

On Wednesday 11/04 we will start with the TV show catalog in the topic slides, on paper.