Database Schema Design and Normalization

Hello! Welcome back to our course.

In the previous lesson, you learned the fundamental principles of database normalization: 1NF, 2NF, and 3NF. We discussed how these rules help you eliminate data redundancy and prevent common issues like insertion, update, and deletion anomalies.

Today, we move from theory to practice. Your goal for this lesson is to take the "why" you've learned and apply it to the "how." You will design a normalized database schema for a given application specification. This is the critical blueprinting phase that every developer must go through before writing a single line of database migration code.

This exercise will directly leverage your understanding of the normal forms and set a solid foundation for the next lesson, where we will implement this schema using Laravel migrations.

1. The Design Process and Our Application

A robust database schema doesn't happen by accident. It's the result of a systematic process. We'll follow these four steps, which are essential for translating business requirements into a logical data model:

1. Analyze Requirements: Understand what data needs to be stored and how it will be used.
2. Define Entities and Attributes: Create an initial, rough draft of the tables and columns.
3. Normalize the Data: Refine the initial draft by applying the rules of 1NF, 2NF, and 3NF to eliminate redundancy and dependencies.
4. Define Keys and Relationships: Finalize the schema by establishing primary keys, foreign keys, and the relationships between tables.

The article "How to Design a Relational Database Schema" from DbSchema provides an excellent overview of this exact process. We will follow its structure throughout this lesson.

Our Application Specification

For our project, we will design the database for a simple e-commerce platform that sells books. Here are the core requirements:

• Users: The system needs to store user accounts with a name, email, and password.
• Products (Books): Each book has a title, a description, a price, and a stock quantity.
• Categories: Books can be assigned to one or more categories (e.g., "Science Fiction", "Biography").
• Orders: Users can place orders. Each order has a total amount, a status (e.g., 'pending', 'shipped', 'delivered'), and is associated with the user who placed it.
• Order Contents: An order must contain one or more books. For each book in an order, we need to store the quantity ordered and the price at the time of purchase.

2. Step 1 & 2: Identifying Entities and an Initial Draft

From the specification, we can identify the main entities (the "nouns"):

• User
• Product (Book)
• Category
• Order

An initial, unnormalized approach might be to cram all this information into a single orders table, much like a spreadsheet.

Unnormalized Orders Table (Violates 1NF)

order_id	order_status	user_name	user_email	products_in_order	categories
101	shipped	Alice	alice@example.com	[{id:5, title:'Dune', qty:1}, {id:12, title:'Foundation', qty:1}]	Science Fiction
102	pending	Bob	bob@example.com	[{id:22, title:'A Brief History of Time', qty:2}]	Science, Non-Fiction

This design is full of problems:

• The products_in_order column contains a list of objects. This is not atomic and violates 1NF.
• The categories column contains a comma-separated list, also not atomic.
• User information is repeated for every order a user makes.
• What if a product's category changes? We'd have to find and update every order that ever included that product.

This is clearly not a viable design. Let's start breaking it down properly.

3. Step 3: Normalizing the Schema to 3NF

We'll now apply the normalization rules systematically to fix our initial design. The goal is to get to Third Normal Form (3NF).

Applying First Normal Form (1NF)

Rule: Ensure values are atomic and there are no repeating groups.

To fix the violations, we must split the data into separate tables.

1. Separate Users: User information doesn't belong in the orders table. We create a users table.
2. Separate Products and Categories: Products are distinct entities. Categories are too.
3. Handle Many-to-Many Relationships:
   • An order can have many products, and a product can be in many orders. We need a "pivot" table, let's call it order_product, to link them.
   • A product can have many categories, and a category can contain many products. We need another pivot table, category_product.

Our first normalized draft looks like this:

• users (id, name, email, password)
• products (id, title, description, price, stock_quantity)
• categories (id, name)
• orders (id, user_id, status, total_amount)
• order_product (order_id, product_id, quantity, price_at_purchase)
• category_product (category_id, product_id)

This schema now satisfies 1NF. All columns hold atomic values.

Applying Second Normal Form (2NF)

Rule: All non-key attributes must depend on the entire composite primary key.

This rule is relevant for our pivot tables, which have composite primary keys.

• category_product: The primary key is (category_id, product_id). There are no other columns, so 2NF is satisfied by default.
• order_product: The primary key is (order_id, product_id).
  • quantity: Depends on both the specific order AND the specific product. This is correct.
  • price_at_purchase: Depends on both the order AND the product. This is also correct. The price of a product can change over time, so we must store the historical price for each order line item.

If we had mistakenly put product.title in the order_product table, it would violate 2NF because the title depends only on the product_id, not on the order_id. This partial dependency would create redundancy. Our current design avoids this.

The schema is already in 2NF.

Applying Third Normal Form (3NF)

Rule: No transitive dependencies (a non-key attribute cannot depend on another non-key attribute).

Let's examine our tables for any transitive dependencies.

• users, products, categories: These look fine. Each attribute depends directly on the table's primary key.
• orders: Contains id, user_id, status, total_amount.
  • What if we had user_email in the orders table? This would be a transitive dependency: orders.id -> orders.user_id -> users.email. The user_email depends on the user_id (a non-key attribute in this table), not directly on the order.id. By keeping user_email only in the users table, we satisfy 3NF.
• The status column is currently a string ('pending', 'shipped'). This is acceptable, but in a larger application, you might want a separate order_statuses table. For our current scope, a string is fine.

Our schema appears to be in 3NF.

For another detailed walkthrough of this normalization process, you can watch the following video. It tackles a much more complex, denormalized dataset and breaks it down into a 3NF schema, which will help solidify your understanding.

4. Step 4: Finalizing the Schema with Keys and Relationships

Now we can formalize our final, normalized schema. We'll define the columns, add timestamps, and clearly mark primary (PK) and foreign (FK) keys.

---

users table

• id (PK, BigInt, Unsigned, Auto-increment)
• name (VARCHAR)
• email (VARCHAR, Unique)
• password (VARCHAR)
• created_at (TIMESTAMP)
• updated_at (TIMESTAMP)

categories table

• id (PK, BigInt, Unsigned, Auto-increment)
• name (VARCHAR, Unique)
• created_at (TIMESTAMP)
• updated_at (TIMESTAMP)

products table

• id (PK, BigInt, Unsigned, Auto-increment)
• title (VARCHAR)
• description (TEXT)
• price (DECIMAL)
• stock_quantity (INT, Unsigned)
• created_at (TIMESTAMP)
• updated_at (TIMESTAMP)

orders table

• id (PK, BigInt, Unsigned, Auto-increment)
• user_id (FK to users.id)
• total_amount (DECIMAL)
• status (VARCHAR, e.g., 'pending', 'processing', 'shipped', 'delivered', 'cancelled')
• created_at (TIMESTAMP)
• updated_at (TIMESTAMP)

category_product table (Many-to-Many Pivot)

• category_id (PK, FK to categories.id)
• product_id (PK, FK to products.id)

order_product table (Many-to-Many Pivot)

• order_id (PK, FK to orders.id)
• product_id (PK, FK to products.id)
• quantity (INT, Unsigned)
• price (DECIMAL) - This is the price at the time of purchase.

This final structure is clean, avoids data duplication, and protects data integrity. For example, if a product's price changes, it won't affect the total_amount of past orders, because we stored the historical price in the order_product table.

Conclusion

In this lesson, you've walked through the practical and systematic process of designing a relational database schema. Starting from a set of business requirements, you identified entities, drafted an initial structure, and methodically applied the rules of normalization to arrive at a clean, robust, and efficient logical model.

Key Takeaways:

• Design is a Process: Good schemas come from a structured approach: Analyze -> Draft -> Normalize -> Refine.
• Entities become Tables: The main "nouns" in your application requirements are strong candidates for database tables.
• Relationships Dictate Structure: One-to-many relationships are handled with foreign keys, while many-to-many relationships require pivot tables.
• Normalization Prevents Anomalies: Applying 1NF, 2NF, and 3NF systematically eliminates redundancy and protects your data's integrity.
• The Blueprint is Ready: The final schema we designed is the logical blueprint we will use to build our physical database.

Next Up:

With our logical schema designed, the next step is to bring it to life. In the upcoming lesson, you will learn how to create and manage database schema using Laravel migrations. You will translate the tables and columns we've designed today into PHP code that Laravel can use to build and modify your MSSQL database.