Tables, Rows, Columns, and a Schema

SQLite gives the lending project a real database engine. We still need to decide how the facts are arranged inside its database. Riley’s email was copied because each CSV row tried to describe a member, an item, and a loan at the same time. A relational database can give each kind of thing its own table, so we will begin with the items people can borrow.

Here are the current rows in the items table:

Now find the projector in this table and read across its row. The three values belong to one item: its number is 3, its name is Projector, and its category is electronics.

A row holds one record. A column gives the same kind of value a name in every row. If you read down the category column, you get tools, events, and electronics.

Show the table’s schema

The rows above are the data stored right now. The table also has a design that says what each row can contain:

Schema for items

That design is the table’s schema. It gives the table its name and gives each column a name and purpose.

Suppose Projector is renamed Portable projector. Only one value in the current rows changes. The schema still has the same three columns.

Now suppose the catalog needs to record whether each item is in good condition. Adding a condition column would change the schema because every item row would gain a new named place for that information.

Different facts, different tables

The finished lending database will give each kind of thing its own table:

We will connect those tables later. For now, stay with items and imagine that someone asks, “What can people borrow?” The answer only needs the names, not every value in every row. In the next lesson, you will ask SQLite to return just the name column.