A Repeatable Design Process

Imagine someone asks you to design a database for a product you barely know yet.

You could start typing CREATE TABLE, but that usually means the schema will reflect your first guess. A better design starts with a repeatable process.

The goal is not to make design mechanical. The goal is to make sure you ask the important questions every time.

Step 1: gather requirements

Start with plain sentences about what the system must remember and what people will do with it.

For a small event system, requirements might say:

• An organizer creates events.
• A venue hosts many events.
• An attendee registers for events.
• A registration has a status and registration time.

In those sentences, nouns hint at things and verbs hint at relationships.

Requirements are never perfect. Write down examples, exceptions, and rules while they are still cheap to change.

Step 2: identify entities

An entity is a kind of thing your system tracks. It usually has its own identity, attributes, and relationships.

From the event requirements, likely entities are:

• organizers
• venues
• events
• attendees
• registrations

registrations may sound like an action, but it deserves a table because it has facts of its own: status, time, and the two things it connects.

Step 3: identify attributes

An attribute is a fact about one entity. Put each fact where it belongs.

Ask whether each attribute describes the row itself. A venue address does not describe one event; it describes the venue. That is why it belongs on venues, not repeated on every events row.

QuestionSelect one

A requirement says, "A venue has a street address, and many events happen at the same venue." Where should the address usually live?

On every events row.

On the venues table.

On the organizers table.

In the application only.

Step 4: identify relationships and cardinality

A relationship says how entities connect. Cardinality says how many rows may be connected.

Ask questions in both directions:

• Can one venue host many events?
• Must every event have one venue?
• Can one attendee register for many events?
• Can one event have many attendees?

The attendee-event relationship is many-to-many, so it becomes the registrations table.

Step 5: choose keys

A primary key gives each row stable identity. A foreign key connects a row to another row.

Most learning schemas use generated integer primary keys because they are simple and stable. Natural facts such as email addresses can still be protected with UNIQUE.

QuestionSelect one

Why might a table use a generated id as the primary key and still make email unique?

Because primary keys and unique constraints do the same job.

Because id is stable for references, while email still must not repeat.

Because foreign keys cannot reference generated columns.

Because PostgreSQL requires every text column to be unique.

Step 6: add constraints

Constraints turn design decisions into database rules. They prevent bad data from becoming everyone else's problem.

Common constraints include:

• NOT NULL for required facts
• UNIQUE for facts that cannot repeat
• CHECK for allowed values or ranges
• REFERENCES for valid relationships

The example rejects waiting because the design only allows reserved and cancelled.

Step 7: normalize

Normalization checks whether each fact is stored in the right place. It reduces duplication and prevents one fact from disagreeing with itself.

If venue names and addresses repeat on every event, changing a venue's address means updating many rows. A normalized design stores the venue once and points events to it.

Step 8: review with real examples

Review the design by walking through real scenarios:

• Create a new event.
• Register the same attendee twice.
• Cancel a registration.
• Change a venue address.
• Delete or archive old data.

A design is ready when the schema can represent valid examples and reject the examples that should be impossible.

Check your understanding

QuestionSelect one

Which step should usually happen before choosing columns and constraints?

Writing every CREATE TABLE statement.

Understanding requirements and candidate entities.

Adding indexes for every column.

Removing all foreign keys.

QuestionSelect one

What does cardinality help you decide?

Which SQL keyword starts a query.

Whether a relationship is one-to-one, one-to-many, or many-to-many.

Whether a column should be uppercase.

Whether a table needs sample data.

QuestionSelect one

Why is reviewing with real examples part of the design process?

It replaces the need for constraints.

It guarantees requirements will never change.

It exposes missing entities, relationships, and rules.

It makes every table have the same columns.

QuestionSelect one

Which statement best describes the whole process?

Start with foreign keys, then invent requirements around them.

Move from requirements to entities, relationships, keys, constraints, normalization, and review.

Normalize first, before knowing what facts exist.

Avoid revisiting earlier decisions.