Understanding Relationships

Every database is built on relationships. There are three types you’ll use constantly:

One-to-Many (1:M) – The Most Common

One thing has many other things.

Author ----< Posts

One author writes many posts
Each post belongs to one author

Real-world examples:

• One customer places many orders
• One category contains many products
• One user writes many comments

How it works in database terms:

• The “many” side stores a foreign key
• posts table has an author_id column

Many-to-Many (M:M) – Needs a Bridge

Many things relate to many other things.

Posts >----< Tags

One post has many tags
One tag applies to many posts

Real-world examples:

• Students enroll in many courses; courses have many students
• Products belong to many categories; categories contain many products
• Users follow many users; users have many followers

How it works in database terms:

• You need a pivot table (also called junction table)
• For posts and tags: create post_tag table
• This table contains: post_id and tag_id

One-to-One (1:1) – The Rare One

One thing has exactly one other thing.

User ---- Profile

One user has one profile
One profile belongs to one user

Real-world examples:

• User has one billing address
• Employee has one desk assignment
• Order has one payment record

When to use:

• Splitting large tables for performance
• Optional data that not all records have
• Security (keeping sensitive data separate)

What Bad Database Design Looks Like

Learn to recognize these anti-patterns:

❌ Multiple Values in One Column

Bad:

products table:
- colors: "red,blue,green,yellow"
- sizes: "S,M,L,XL"

Why it’s bad: You can’t search for “all red products” efficiently. You can’t sort. You can’t validate.

Good:

products table: id, name, price
product_colors table: product_id, color
product_sizes table: product_id, size

❌ Repeating Columns

Bad:

users table:
- phone_1
- phone_2
- phone_3

Why it’s bad: What if someone has 4 phones? What if most have 1? Lots of empty columns.

Good:

users table: id, name, email
phone_numbers table: user_id, phone_number, type (mobile/home/work)

❌ Storing Calculated Data

Bad:

order_items table:
- quantity: 5
- unit_price: 20.00
- total_price: 100.00  ← Calculated value stored

Why it’s bad: What if unit_price changes? You now have inconsistent data.

Good: Calculate quantity * unit_price in your queries or application code.

Exception: Sometimes you DO store calculated data for performance or historical accuracy (e.g., the price at time of purchase), but be intentional about it.

❌ Vague or Generic Names

Bad:

• data
• info
• temp
• value
• status (status of what?)

Good:

• email_verified_at
• shipping_address
• payment_status
• order_status

The Essential Columns Checklist

Every table you design should have these:

✅ Primary Key

id - UNSIGNED BIGINT, AUTO_INCREMENT

Uniquely identifies each row. Always include this.

✅ Timestamps

created_at - When was this record created?
updated_at - When was it last modified?

You’ll thank yourself later when debugging or auditing.

✅ Foreign Keys for Relationships

author_id - References the authors table
category_id - References the categories table

These create the connections between tables.

✅ Consider Soft Deletes

deleted_at - NULL means active, timestamp means "deleted"

Sometimes you need to “undelete” something. Soft deletes let you mark records as deleted without actually removing them.

Normalization: The Practical Version

Forget the formal definitions (1NF, 2NF, 3NF) for now. Follow these simple rules:

Rule 1: One Fact Per Column

Each column should contain one piece of information.

Bad: full_name: "John Doe"
Good: first_name: "John" and last_name: "Doe"

Rule 2: No Repeating Information

Don’t duplicate the same information across multiple rows.

Bad:

orders table:
order_id | customer_name | customer_email | customer_phone
1        | John Doe      | john@email.com | 555-1234
2        | John Doe      | john@email.com | 555-1234

Good:

customers table:
id | name     | email          | phone
1  | John Doe | john@email.com | 555-1234

orders table:
id | customer_id
1  | 1
2  | 1

Rule 3: Related Information Stays Together

Information about the same thing should be in the same table.

Example: Customer’s name, email, and address all belong in the customers table, not scattered across different tables.

Paper Design Exercise

Let’s design a database for a simple Task Management Application.

Requirements:

• Users can create projects
• Projects contain tasks
• Tasks can be assigned to users
• Tasks have a status (pending, in progress, completed)
• Tasks have a priority level
• Users can comment on tasks

Step 1: Identify Entities (Tables)

Draw boxes for each main entity:

┌──────────┐    ┌──────────┐    ┌──────────┐    ┌──────────┐
│  users   │    │ projects │    │  tasks   │    │ comments │
└──────────┘    └──────────┘    └──────────┘    └──────────┘

Step 2: List Attributes (Columns)

┌─────────────────┐
│     users       │
├─────────────────┤
│ id              │
│ name            │
│ email           │
│ password        │
│ created_at      │
│ updated_at      │
└─────────────────┘

┌─────────────────┐
│    projects     │
├─────────────────┤
│ id              │
│ name            │
│ description     │
│ owner_id        │ ← FK to users
│ created_at      │
│ updated_at      │
└─────────────────┘

┌─────────────────┐
│     tasks       │
├─────────────────┤
│ id              │
│ title           │
│ description     │
│ project_id      │ ← FK to projects
│ assigned_to     │ ← FK to users
│ status          │
│ priority        │
│ due_date        │
│ created_at      │
│ updated_at      │
└─────────────────┘

┌─────────────────┐
│    comments     │
├─────────────────┤
│ id              │
│ task_id         │ ← FK to tasks
│ user_id         │ ← FK to users
│ content         │
│ created_at      │
│ updated_at      │
└─────────────────┘

Step 3: Draw Relationships

users (1) ----< (M) projects     "A user owns many projects"
projects (1) ----< (M) tasks     "A project has many tasks"
users (1) ----< (M) tasks        "A user is assigned many tasks"
tasks (1) ----< (M) comments     "A task has many comments"
users (1) ----< (M) comments     "A user writes many comments"

Step 4: Test With Questions

Can your design answer these questions?

• ✓ “Show all tasks in project X”
  → Query tasks where project_id = X
• ✓ “Show all tasks assigned to user Y”
  → Query tasks where assigned_to = Y
• ✓ “Show all comments on task Z”
  → Query comments where task_id = Z
• ✓ “Show all projects owned by user A”
  → Query projects where owner_id = A

If you can’t answer a business question with your current design, you need to revise.

Your Turn: Practice Exercise

Design a database for an Online Bookstore. Include:

Requirements:

• Customers can browse and purchase books
• Books have authors, publishers, and categories
• Customers can leave reviews and ratings
• Track order history and shipping addresses
• Books can be on sale with discounts

Your Task:

1. List all the entities (tables) needed
2. For each table, list 5-7 key columns
3. Draw the relationships between tables
4. Identify which relationships are 1:M and which are M:M
5. Write 5 questions your application needs to answer and verify your design supports them

Spend 20-30 minutes on this before looking at any solution.

Common Pitfalls to Avoid

Over-Engineering

Don’t design for every possible future feature. Design for what you need now, plus one step ahead.

Bad: “What if we need to track book lending history across 50 libraries with federated authentication and blockchain verification?”

Good: “We need books, authors, customers, and orders. Maybe support multiple addresses per customer.”

Under-Engineering

Do think one step ahead for obvious scenarios.

Bad: “Each user has one address” → What about shipping vs. billing?

Good: “Users can have multiple addresses with a type (shipping/billing)”

Forgetting Audit Requirements

For important business data, consider:

• Who made the change? (user_id)
• When was it changed? (updated_at)
• What was the old value? (audit log table)

Ignoring Soft Deletes

Do you need to “undelete” things? Add deleted_at.

Key Takeaways

Before you write a single line of Laravel code:

✓ Start with the four questions: What, Attributes, Relationships, Queries
✓ Use the noun test to find entities
✓ Draw on paper – boxes and lines, not code
✓ One table = one type of thing – don’t mix concepts
✓ Think about relationships – foreign keys connect everything
✓ Test with questions – can your design answer them?
✓ Iterate – first draft is never perfect

Remember: A few hours of design thinking can save you weeks of refactoring. Your future self will thank you.

Next Steps

In the next lesson, we’ll take these paper designs and learn when and how to translate them into Laravel migrations. But the thinking we did here? That’s the foundation everything else builds on.

For now, practice. Take any application you use daily and sketch its database design. You’ll be surprised how much you learn just by thinking it through.

“Weeks of coding can save you hours of planning.” – Anonymous Developer (learning the hard way)