Quick SummaryGood database design starts with organizing your information into logical groups, like putting customer details in one table and order history in another. These groups are then connected through common identifiers using a table reference field - like a customer ID that links a person to all their orders - which helps maintain accuracy and avoid duplicating information.
HintUse a tool like Excalidraw to help you when designing your database.

Using Excalidraw to begin the database design process.

Table Relationships
To illustrate multiple examples, we’ve added a Publishers table to our
visualization.
- Each thing on one side matches exactly one thing on the other side. Like a person and their social security number: one person has one number, and each number belongs to one person
- One thing on one side can connect to multiple things on the other side, but those multiple things each only connect back to one thing. Like a mother and her children: one mother can have many children, but each child has only one mother
- Things on both sides can connect to multiple things on the other side. Like students and classes: one student takes many classes, and each class has many students
Database Fields
Think about the essential characteristics that describe each thing. Just like how a person’s profile might include their name, birthday, and contact info, each table should contain the core pieces of information that defines that thing. When referring to all of the fields in a database table as a whole, we’ll call this schema. Let’s use our bookstore example:- For Books: You’ll want the ISBN (like a book’s fingerprint), title, publication date, current price, and maybe format (hardcover/paperback). You don’t need to store the author’s name here - that’s what the connection to the Authors table is for.
- For Authors: You’ll store their name, perhaps birth date, nationality, and a brief biography. You don’t need to store a list of their books - the relationship between tables handles that.
- For Customers: You’ll want their name, contact information, shipping addresses, and maybe their preferences or a membership status. You don’t need to store their purchase history here - that’s tracked through the Sales table.
Planning for the Future
Think about what information you’ll need to find quickly. Just as you might keep frequently used items in easily accessible drawers, consider what data you’ll search for most often. This helps you decide how to organize and index your information. Think about how information might expand in the future. Initially, you might only need basic book formats (hardcover and paperback). But what happens when you want to add audiobooks? You’ll need new fields like runtime, narrator, and audio format. Instead of hard-coding format types, you could create a separate formats table, and use a table reference in your books table that lets you add new types without changing your core structure. Suppose you create a database table for storing book formats directly within the books table: Books TableBookID | Title | Author | Hardcover | Paperback | Audiobook |
---|---|---|---|---|---|
BookID | Title | Author |
---|---|---|
FormatID | FormatType |
---|---|
BookID | FormatID |
---|---|