Database Design Basics: Tables, Keys, and Relationships

Database Database Design

Good database design makes an application easier to understand, faster to query, and safer to change. The goal is to model real-world information without unnecessary duplication.

Primary and Foreign Keys

A primary key uniquely identifies a record. A foreign key references a record in another table, creating a relationship between tables.

CREATE TABLE courses (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);

CREATE TABLE enrollments (
  id INTEGER PRIMARY KEY,
  course_id INTEGER NOT NULL,
  student_id INTEGER NOT NULL,
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

One-to-Many Relationships

One course can have many enrollments, while each enrollment belongs to one course. Junction tables are useful when both sides can have many related records.

Practical Design Checklist

Give every table a stable primary key
Use foreign keys for valid relationships
Avoid storing the same fact in many places
Choose clear names for tables and columns
Add indexes to fields used often for searching

Conclusion

Start with a clear data model, then use real queries and sample data to improve the design as your application grows.