Table Design | Learn IT Fundamentals with Teacher Pochi

Key points

Table design is the work of deciding the shape of the tables that store your data.
"Columns" define the data's fields, a "primary key" uniquely identifies each row, and "foreign keys" connect tables to one another.
Poor design leads to duplicated or inconsistent data and slower searches.

What is a table?

A table is data organized into rows and columns. A single table holds one type of data together — for example, "customers" or "orders."

customers
table

A collection of
customer data

Row × column
layout

Teacher Pochi's hintThink of a company's "customer ledger." One ledger is one table, and the format of the page you fill in corresponds to the column design.

Columns and data types

A column represents one field of data stored in a table. For each column, you decide a "data type" that specifies what kind of data it holds.

id: integerA numeric column

name: stringA string column

created_at: datetimeA datetime column

Fixing the data type in advance prevents bad data from slipping in — like text ending up in a column that should only hold numbers.

Teacher Pochi's hintThink of the blanks on a survey form. The "age" field only accepts numbers, and the "name" field only accepts letters — each blank has a fixed idea of what can be written in it.

Primary keys: uniquely identifying a record

A primary key is a column used to pinpoint a single row (record) within a table without any duplication. It guarantees that no two rows share the same value.

id: 1
John Smith

id: 2
Jane Doe

id is never
duplicated

Teacher Pochi's hintThis resembles a student ID number. Even if two students share the same name, checking the student ID number lets you identify exactly who's who.

Foreign keys: connecting tables together

A foreign key is a mechanism that links two tables together by storing one table's primary key value as a column in another table.

customers
primary key: id

customer_id

orders
foreign key: customer_id

This lets the data correctly express "which customer this order belongs to," without having to copy the customer's name and address into the orders table every time.

Teacher Pochi's hintThis is similar to the "member number" printed on a courier's delivery slip. The slip itself doesn't need to spell out the full address — looking up the member number in the membership database gives you the correct delivery destination.

Relationships between tables: one-to-many

A relationship that comes up constantly in table design is the "one-to-many" relationship. Let's consider the example of one customer placing multiple orders.

One customer

One-to-many

Multiple
orders

This relationship is expressed by giving the "many" side table (orders) a foreign key that holds the "one" side table's (customer) primary key. There are more complex relationships too, such as "many-to-many," but it's worth getting comfortable with one-to-many first.

Practical pitfalls to watch for

Not accounting for indexesIf you don't add an index to a column that's frequently used in search conditions, queries get slower and slower as the table grows.

Leaving nullability ambiguousNot deciding whether a column can be NULL — leaving "no value entered" indistinguishable from "the value is zero" — becomes a source of bugs down the line.

Standardize naming conventionsAgreeing on table and column naming conventions across the team (singular vs. plural, snake_case, and so on) improves code readability and maintainability.

Summary

Table design is the work of using columns to define a table's fields, a primary key to uniquely identify each row, and foreign keys to connect tables to one another. If this foundation isn't solid, duplication and inconsistencies tend to creep in later.