Designing a schema starts by identifying entities (customers, orders, products) and the relationships between them (one-to-many, many-to-many), then translating that into normalized tables with primary and foreign keys enforcing those relationships. Indexes are added deliberately based on expected query patterns, not preemptively on every column. Getting this right up front matters because changing a live schema under real data and traffic is far riskier and slower than getting the design right before the first row is ever written.
One-to-many: a foreign key on the "many" side
One customer has many orders; the foreign key lives on the orders table, since each order belongs to exactly one customer, but a customer can have any number of orders.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id), -- the "many" side
total NUMERIC(10, 2)
);Many-to-many: a join table in between
Neither table can hold a simple foreign key for a many-to-many relationship (a student takes many courses, a course has many students) — a separate join table with two foreign keys, and often a composite primary key across both, models the relationship correctly.
CREATE TABLE students (id INTEGER PRIMARY KEY, name VARCHAR(100));
CREATE TABLE courses (id INTEGER PRIMARY KEY, title VARCHAR(100));
CREATE TABLE enrollments ( -- the join table
student_id INTEGER REFERENCES students(id),
course_id INTEGER REFERENCES courses(id),
enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (student_id, course_id) -- composite key: no duplicate enrollments
);Design as a checklist, before writing application code
Identify every entity and its attributes; decide each relationship's cardinality (one-to-one, one-to-many, many-to-many); normalize to at least 3NF unless there's a specific, deliberate reason not to; add NOT NULL/CHECK/UNIQUE constraints for every real business rule the data must satisfy; and add indexes based on the queries the application will actually run — not speculatively on everything. Every one of these decisions is far cheaper to get right before the schema holds real production data.