DataslopeWhat Database Design Actually Is
Define database design as choosing tables, relationships, and rules before application code depends on them.
Before you build a library app, you can start coding screens: add a book form, a borrower form, a checkout button. But sooner or later the code must answer design questions. What is a book? Is a physical copy different from a title? Can one borrower have many loans? What should happen if a loan points to a missing copy?
Database design is the work of answering those questions before the rest of the software quietly answers them in inconsistent ways.
It means deciding what tables exist, what each table represents, how those tables relate, and what rules protect the data.
Design is not just writing CREATE TABLE
SQL syntax is how you express a design. It is not the design itself. The design is the set of modeling decisions behind the SQL:
- Which real-world things become tables?
- Which facts become columns?
- Which rows need stable identity?
- Which tables point to other tables?
- Which values must be present, unique, or limited?
A schema is good when those choices match the meaning of the system and make invalid data difficult to store.
What tables represent
A table should usually represent one kind of thing or one kind of
relationship. In a library, books might store titles such as
Database Design Basics. But if the library owns three physical copies
of that title, those copies may need their own table because each copy
has a barcode and can be loaned separately.
That is design thinking: not "how many columns can fit in one table?" but "what distinct things does the system need to remember?"
This design separates a book title from a physical copy. That choice matters because a loan is about a copy, not just a title.
Quick check
Which question is most clearly a database design question?
What color should the checkout button be?
Which cloud region should host the server?
Should library loans point to book titles or physical book copies?
Which font should reports use?
Conceptual, logical, and physical design
Design happens at different levels. Beginners often mix these together, so it helps to separate them.
Conceptual design names the important things in the real world. It is close to plain language: customers place orders; orders contain products; authors write posts.
Logical design turns those ideas into relational structure: tables, columns, primary keys, foreign keys, and constraints. This is the main focus of this course.
Physical design concerns how a particular database stores and runs that schema: indexes, storage settings, partitioning, and operational choices. Those topics matter, but they are not our focus here.
You will see PostgreSQL syntax because schemas must eventually become real. But the course is about the logical shape: what the tables mean and what rules preserve that meaning.
Logical design protects meaning
Here is a small logical design for blog posts and comments. Notice that the design does more than store text. It says each comment belongs to one existing post, each post has a unique slug, and required fields cannot be missing.
The important part is not that this is the only possible blog schema. It is that the schema makes clear promises about identity, relationships, and allowed values.
Quick check
Which level is this course mainly about?
Physical design, such as storage parameters and server tuning.
Logical relational design, such as tables, keys, relationships, and constraints.
Frontend component design.
Backup scheduling and monitoring.
Design happens before code depends on it
Application code tends to harden around the schema. If the first version stores product names as free text on orders, many functions may start assuming that is where product information lives. Changing the schema later then means changing code, migrations, tests, reports, and possibly old data.
Design first does not mean you must predict everything. It means you pause long enough to model the core facts that are already visible.
The schema becomes a contract. It is much easier to make that contract clear before many callers depend on it.
What this course means by design
In this course, database design means:
- Finding entities: customers, orders, products, posts, comments.
- Choosing attributes: names, dates, quantities, statuses.
- Defining relationships: customers place orders, posts have comments.
- Choosing keys: stable identities for rows.
- Adding constraints: rules that reject impossible or unsafe data.
- Revising the model as requirements become clearer.
It does not mean becoming a PostgreSQL administrator. We will not focus on replication, backups, server memory, or query tuning. Those are important jobs, but they are different from deciding what the data means.
Check your understanding
What is the best plain-English definition of database design?
Choosing the fastest database server hardware.
Writing queries after all tables already exist.
Deciding what tables, relationships, and rules should represent the system's facts.
Picking random column names and fixing them later.
In a library schema, why might book_copies be separate from books?
Because every table must have exactly two columns.
Because PostgreSQL cannot store book titles.
Because a title and a physical copy are different things with different facts.
Because joins should always be avoided.
Which item belongs most clearly to logical design?
Choosing a backup retention policy.
Adding a foreign key from comments.post_id to posts.id.
Deciding how much RAM the database server needs.
Selecting the color palette for the admin page.
Why is it useful to design before writing much application code?
Application code cannot be written until every future feature is known.
Code quickly starts depending on whatever schema shape exists.
PostgreSQL refuses schemas created after code exists.
Designing first removes the need for future schema changes.