Project Brief and Schema Design

Objectives

By the end of this lesson, you should be able to:

• Read a set of real requirements and identify the entities involved
• Decide which relationships (Module 9) connect those entities
• Sketch a schema before writing a single CREATE TABLE statement

💡 Why this matters: Every lesson so far started with a table already decided. A real project starts with a paragraph of requirements and nothing else, translating that into a schema is its own skill, separate from writing the SQL itself.

The Brief

Fernwood Supply Co. sells office products (pens, organizers), electronics (mice, keyboards), and furniture (desks, lamps) to business customers. They need a database to track:

Products, organized into categories, each with a price and whether it’s currently in stock. Customers, each with a name, email, and city. A sales team of employees, each belonging to a department, each reporting to a manager (except the top of the hierarchy), and each having an internal profile with a short bio. Orders, each placed by one customer, handled by one sales employee, with a status (pending, completed, or cancelled), and containing one or more products, each with its own quantity and the price at the time of purchase.

Identifying the Entities

Six things need their own table: categories, products, customers, employees, employee profiles, and orders. A seventh, less obvious one is needed too: an order containing “one or more products, each with its own quantity” is a relationship with its own data attached (quantity, price at time of purchase), not just a plain connection, this needs a junction table (Module 9), order_items.

Deciding the Relationships

Reading the brief closely, matching each sentence to a relationship shape from Module 9:

“Products, organized into categories” is one category, many products, a one-to-many from categories to products. “Each reporting to a manager” is a self join relationship, employees referencing itself. “Each having an internal profile” is a one-to-one between employees and employee_profiles, one profile per employee. “Orders, each placed by one customer, handled by one sales employee” are two more one-to-many relationships, from customers to orders, and from employees to orders. “Containing one or more products, each with its own quantity” is a many-to-many between orders and products, implemented through the order_items junction table.

Sketching Before Building

Before writing any SQL, it’s worth listing every table with its planned columns, in plain terms:

categories:        id, name
products:          id, name, category_id (-> categories), price, in_stock
customers:         id, name, email, city
employees:         id, first_name, last_name, department, manager_id (-> employees), hire_date
employee_profiles: id, employee_id (-> employees, one-to-one), bio
orders:             id, customer_id (-> customers), employee_id (-> employees), order_date, status
order_items:       order_id (-> orders), product_id (-> products), quantity, unit_price

This sketch is deliberately informal, no data types, no constraints yet, just entities and how they connect. Getting the shape right first (which tables exist, what points at what) makes the next lesson, actually writing CREATE TABLE statements with real constraints, much more mechanical.

Try It

1. Re-read the brief and confirm, in your own words, why order_items needs to exist as its own table rather than a column on orders.
2. Identify which relationship in this schema is one-to-one, and which column enforces it (a preview of the next lesson).
3. Identify which relationship is a self join, and explain what real-world hierarchy it models.
4. Sketch one additional table this business might plausibly need later (for example, product reviews, or shipping addresses), and describe in plain terms how it would connect to the existing schema.

Recap

• Turning requirements into a schema starts with identifying entities (nouns needing their own table) and relationships (how those entities connect).
• A relationship carrying its own data (like an order item’s quantity and price) needs a junction table, not just a plain foreign key.
• Sketching table names and columns informally, before writing any SQL, catches structural decisions early, before constraints and syntax add complexity.

Next lesson: building this schema for real, with every CREATE TABLE statement, constraint, and relationship in place.