Designing your database

TL;DR#

Tonight 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 to keep: when you are not sure where a fact belongs, ask what one row of each table stands for, and store every fact in one place only.

The slides changed after class#

The topic slides were revised tonight, so some examples in the recording differ from the ones in the slides. The examples now follow one customer, Ricardo Adocicados, and the invoice exercise is order 10287, whose lines carry different discounts. The ideas are the same.

Where a fact belongs#

One fact, one place. A sales report that repeats the customer's phone number on every line has to be changed on thousands of 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; it becomes a column. When you are unsure, ask whether the business could have many of it, each with facts of its own.

Why the price is kept on the order line. The products table holds today's list price. Prices change, and what a customer paid last year must not change with them, so each order line keeps the price charged that day. The discount is a column of its own on the line, because two lines of one order can carry different discounts. In Northwind, Pavlova sold at $13.90 on 13 orders and at $17.45 on 7891.

The order's totals. The amount on each line, the subtotal and the total due come from the lines and the freight. You can store the totals on the order or compute them when you need them; if you store them, they are a second copy of what the lines already say, and they have to be updated whenever a line changes.

Keys#

• A primary key names exactly one row. No two rows share it, and it is never empty.
• Use a surrogate key in general: an ID column you add when you design the table, such as product_id. A value the business already uses, such as a name, an email or a stock ticker, can change. When a pair of columns must not repeat, such as a user and a post in a table of likes, keep that pair unique beside the ID.
• 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.
• A foreign key holds another table's primary key, on the many side. An order holds its customer's ID.
• A foreign key refuses an ID that does not exist, and it does not refuse an empty one. An order with no customer at all is stopped by NOT NULL, which we will cover on Tuesday 11/03. DuckDB enforces both.

Relationships#

• Read a relationship aloud in both directions. "A customer places many orders; an order has one customer" is one to many, and the foreign key goes on the many side.
• One to one is usually one table. Split it only when some rows lack the facts, fewer people may read them, or 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, keyed by the order and the product together. Students and courses, employees and projects, and playlists and songs work the same way.

Getting data from a platform#

Tonight's lightning talk showed a job tracker that runs daily on GitHub Actions with a cap on how many tokens each run may spend. It led to a question about getting job posts from LinkedIn.

• Check the platform's API page first, such as LinkedIn's developer site. Access to most platforms' data, LinkedIn's job posts included, is now paid or restricted.
• Third-party scrapers exist, listed on marketplaces such as RapidAPI. Read the platform's terms before you build on one; scraping a site that forbids it can get your account or your project shut down.
• A tool only for yourself is a good project. Start by describing the need to an agent and letting it ask you questions until the scope is clear.

How AI models work#

Recommended in class:

• Andrej Karpathy's videos, long and detailed, on how models such as GPT are built
• Andrew Ng's courses: Machine Learning Specialization, AI For Everyone and Generative AI for Everyone

Review from last week#

• A POST carries its data in the body of the request.
• 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.

Read the files before you commit#

An API key ended up in a public repository this week, after a repository was moved into a folder that held much more than the course work. Before every commit, read the list of changed files. If it shows files you never made, leave them out and ask me.

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.

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