Chapter 7: Basic Terminology and Naming Conventions — Tables, Columns, Primary Keys, Foreign Keys
What you'll learn in this chapter
- Explain, in your own words, the differences between tables, columns, records, primary keys, and foreign keys
- Write table definitions that follow globally standard naming conventions (
snake_case, plurals, theis_prefix, etc.) - Understand the role of related terms like schema, index, and view
You should understand Chapter 4: CRUD and ACID and Chapter 5: Introduction to Relational Databases.
The Big Picture of Basic Terminology
Looking at the components of a relational database as a hierarchy gives you this.
Let's look at a concrete example.
Database: company_db
└── Schema: public
├── Table: employees
│ ├── Column: id (INTEGER, PRIMARY KEY)
│ ├── Column: name (VARCHAR(100), NOT NULL)
│ ├── Column: email (VARCHAR(255), UNIQUE)
│ └── Column: department_id (INTEGER, FOREIGN KEY)
│
└── Table: departments
├── Column: id (INTEGER, PRIMARY KEY)
└── Column: name (VARCHAR(50))
Schema: a "namespace" that groups tables and indexes together. You can create multiple schemas within a single database to logically separate tables. PostgreSQL has a public schema by default. In MySQL, "schema" and "database" are nearly synonymous (with a subtle difference), which often trips up beginners.
The Structure of a Table
Let's look at a table's structure visually.
employees table
+----+---------------+----------------------+---------------+
| id | name | email | department_id |
|(PK)| | (UNIQUE) | (FK) |
+----+---------------+----------------------+---------------+
| 1 | Taro Yamada | yamada@example.com | 10 | ← record (row)
| 2 | Hanako Suzuki | suzuki@example.com | 20 |
| 3 | Jiro Sato | sato@example.com | 10 |
+----+---------------+----------------------+---------------+
^ ^
| |
column header cell (individual value)
| Concept | What it is |
|---|---|
| Table | A two-dimensional grid for storing data |
| Column | A vertical item (e.g., id / name / email) |
| Record (row) | One horizontal unit of data |
| Cell | An individual value at the intersection of a row and column |
Formal Term Names and Aliases
Database terminology is called different things depending on the context or the textbook. This guide mainly uses the term in the "term" column below, but it helps to know the aliases too, so you don't get confused.
| Term | Common aliases | What it refers to |
|---|---|---|
| Table | Relation | The basic unit for storing data |
| Column | Field / Attribute | An item of data |
| Record | Row / Tuple | One unit of data |
| Primary key (PK) | - | The value that uniquely identifies a record |
| Foreign key (FK) | - | A value that references another table |
| Schema | - | A namespace that groups tables and the like |
| Index | - | A data structure for speeding up searches |
| View | Virtual table | A "view" derived from an existing table |
The details of primary keys and foreign keys are covered in Chapter 10: Key Concepts. Here's a rough overview:
- Primary key (PK): a value that's uniquely determined within a table — "this row is this one" (e.g.,
users.id) - Foreign key (FK): a value that points to another table's primary key (e.g.,
orders.user_id→users.id)
Foreign keys let you trace "whose order this is" through the relationship between tables.
Naming Conventions — Globally Standard Practices
Database naming is hard to change later, so it's important to agree on "rules the team follows" up front. Here are conventions that are widely used around the world.
Table names
-- Good examples
CREATE TABLE users (...);
CREATE TABLE products (...);
CREATE TABLE order_items (...);
CREATE TABLE user_addresses (...);
-- Bad examples
CREATE TABLE User (...); -- starts with a capital letter
CREATE TABLE tbl_user (...); -- unnecessary tbl_ prefix
CREATE TABLE ユーザー (...); -- Japanese (only works on some DBMSs)
CREATE TABLE usr (...); -- an unclear abbreviation
| Rule | Reason |
|---|---|
Plural (users, etc.) | A table represents a set of records (multiple users) |
Lowercase + snake_case | Many RDBMSs can be configured to be case-sensitive or not; sticking to lowercase is safe |
| Avoid abbreviations | So you (in six months) or someone else can understand it at a glance |
| Avoid Japanese | Avoids compatibility issues with DBMSs, tools, and libraries |
Column names
-- Good example
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price NUMERIC(10, 2),
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN NOT NULL DEFAULT TRUE -- Booleans start with is_
);
-- Bad example
CREATE TABLE products (
id INTEGER PRIMARY KEY,
Name VARCHAR(200), -- starts with a capital letter
ProductPrice NUMERIC(10, 2), -- camelCase
作成日時 TIMESTAMPTZ, -- Japanese
flg BOOLEAN -- an unclear abbreviation
);
Naming That's Globally Standard
| Purpose | Recommended name | Description |
|---|---|---|
| Primary key | id (or <tablename_singular>_id) | A unique identifier within the table. Either plain id, or an explicit form like user_id when it's often referenced externally |
| Creation timestamp | created_at | When the record was created |
| Update timestamp | updated_at | When the record was last updated |
| Delete flag | is_deleted or deleted_at (logical delete via a timestamp) | Used for logical deletes |
| Active flag | is_active | Whether the record is active or inactive |
| Display order | display_order or sort_order | Controls sort order |
| Creator | created_by (a user ID, etc.) | For audit logs |
| Foreign key | <referenced_table_singular>_id | Makes the referenced table explicit, as in orders.user_id |
id or user_id?In a context confined to a single table, keeping it simple as id is common; in a context where you JOIN multiple tables, it's common to make the reference explicit on the foreign-key side, as in users.id and orders.user_id. ORMs like Rails or Laravel default to assuming the primary key is id, so following that convention is safe when adopting a framework.
An Overview Map of Design Terminology
Here's a map of the terms covered in later chapters of this guide. You don't need to memorize all of it right now — it's enough at this point to know "these kinds of terms exist."
A Real Table Definition Example
Let's check the terms covered so far using an e-commerce site's product management as an example.
-- Create the database (usually done once, by infrastructure)
CREATE DATABASE ecommerce_db;
-- Create a schema (optional)
CREATE SCHEMA shop;
-- Categories table (master data)
CREATE TABLE shop.categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
parent_category_id INTEGER REFERENCES shop.categories(id), -- self-referencing foreign key
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- Products table
CREATE TABLE shop.products (
id SERIAL PRIMARY KEY,
code VARCHAR(50) UNIQUE NOT NULL,
name VARCHAR(200) NOT NULL,
description TEXT,
category_id INTEGER NOT NULL REFERENCES shop.categories(id),
price NUMERIC(10, 2) NOT NULL CHECK (price > 0),
stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- Indexes (to speed up searches)
CREATE INDEX idx_products_category ON shop.products(category_id);
CREATE INDEX idx_products_active ON shop.products(is_active);
-- A view (saves a frequently written SELECT as a "view")
CREATE VIEW shop.active_products AS
SELECT p.id, p.name, p.price, c.name AS category_name
FROM shop.products p
JOIN shop.categories c ON p.category_id = c.id
WHERE p.is_active = TRUE
AND p.stock_quantity > 0;
A view is like a "saved SELECT statement." From the application side, you can write SELECT * FROM shop.active_products just like a normal table, but underneath, it's a virtual table derived from the original tables. Instead of writing the same complex query over and over, creating one view keeps your code cleaner.
Exercises
Point out at least four problems with the following table definition, and write a corrected version.
CREATE TABLE User (
UserID INTEGER PRIMARY KEY,
UserName VARCHAR(100),
Email VARCHAR(255),
ProductCount INTEGER,
flg BOOLEAN,
作成日 TIMESTAMPTZ
);
Sample answer
Problems and fixes:
| Problem | Fix |
|---|---|
The table name starts with a capital letter and is singular (User) | users (lowercase, plural) |
Column names are camelCase (UserID, UserName) | id, name (snake_case) |
The primary key name redundantly repeats the table name (UserID) | id (self-contained within the table) |
ProductCount on the users table is an aggregate value, which may violate normalization | Drop the column entirely and aggregate on demand with SELECT COUNT(*) FROM products WHERE user_id = ? |
flg is an unclear abbreviation | Use a meaningful Boolean name, like is_active |
作成日 is in Japanese | created_at |
The primary key is INTEGER (risk of running out in the future) | Use SERIAL or BIGINT |
No NOT NULL constraint | Add NOT NULL to required fields |
Corrected version:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
A common mistake: casually adding an aggregate column like product_count because it's "convenient." Since an aggregate value can be derived by computing from the original data, storing it risks it drifting out of sync with the source data (for example, if a product is added or removed but the count doesn't get updated). The principle is "compute aggregates on demand with a query"; only introduce denormalization carefully, and only when performance truly requires it (see Chapter 11: Normalization).
Fill in the blanks in the following sentences.
- The value that uniquely identifies each record within a table is called [____].
- A column that references another table's primary key is called [____].
- A namespace that logically groups multiple tables is called [____].
- A data structure for speeding up searches is called [____].
- A saved "view" derived from an existing table is called [____].
Sample answer
- Primary key (PK)
- Foreign key (FK)
- Schema
- Index
- View
Additional note: on #4, it's worth remembering that "an index is automatically created for the primary key." A primary-key lookup like SELECT * FROM users WHERE id = 1 is always fast. Conversely, for "columns you search frequently other than the primary key" (like email or created_at), you need to explicitly create an index.
Summary
Here's a recap of what this chapter covered.
- A database is structured as the hierarchy "database → schema → table → column / record"
- Terms have multiple names (table = relation), so you'll need to get used to this as you read the literature
- The globally standard naming conventions are "lowercase, snake_case, plural (for tables), avoid abbreviations, avoid Japanese"
- Don't casually add derivable columns like aggregate values (the principle of normalization, covered in detail in Chapter 11)
The next chapter covers the idea of classifying data into two kinds — master data and transaction data.