Mapping Product Concepts to Database Schema

Hello! Welcome to your first lesson in the "Learn SQL for Product Designers" course.

I'm excited to get started. Given your extensive background in product design and strategy, you're already skilled at thinking about users, their actions, and the features they interact with. This lesson is all about bridging that knowledge with the world of data.

Lesson 1: From Product Concepts to Database Tables

Today's Goal: By the end of this 60-minute lesson, you will be able to map familiar product concepts like 'users' and 'events' to their underlying structure in a database: tables, columns, and rows. This is the foundational step before you can ask meaningful questions with SQL.

We'll cover:

1. What a database is and why it's relevant to your work.
2. The core components: tables, columns, and rows.
3. How these components represent real-world product concepts.
4. How different pieces of information are linked together.

Let's begin.

---

1. What is a Database?

At its core, a database is simply an organized collection of data, stored electronically. For a product designer, it's the source of truth for everything happening in your product: who your users are, what they are clicking, what they are buying, and when they are active.

Most modern applications use a relational database. The "relational" part just means the data is stored in a structured way across multiple tables that are linked to one another. Think of it like a highly organized and interconnected set of spreadsheets.

To get a feel for why this matters in a product role, let's watch a short introductory video.

As the video mentions, being able to access this data directly allows you to move beyond assumptions and gain concrete insights into user behavior, which is invaluable for informing your design decisions.

2. The Building Blocks: Tables, Columns, and Rows

Now, let's break down the structure of a relational database. Data is organized into three main components:

• Tables (or Entities): A table represents a single, distinct subject. If you were designing a new e-commerce feature, you would likely have a users table, a products table, and an orders table. Each table holds information about one specific type of thing.

• Columns (or Attributes/Fields): Columns are the properties or characteristics of the subject of the table. For a users table, the columns would define what information you store about each user, such as user_id, email_address, signup_date, and country.

• Rows (or Records): A row represents a single, individual entry in a table. Each row in the users table would correspond to one specific user, with their specific user_id, email_address, etc., filled in.

This image provides a simple visual of how these concepts fit together.

The following video segment provides a more detailed walkthrough of this structure and explains an important principle called "normalization"—the practice of splitting data into separate tables to avoid repetition and keep the data clean and efficient.

3. Connecting the Dots: Primary and Foreign Keys

You might be wondering: if we split all our data into separate tables, how do we know which order belongs to which customer? This is where relationships come in, managed by keys.

• Primary Key (PK): A primary key is a column (or set of columns) that contains a unique identifier for each row in a table. For example, in a users table, the user_id would be the primary key. No two users can have the same user_id. This ensures we can always identify a specific user precisely.

• Foreign Key (FK): A foreign key is a column in one table that refers to the primary key in another table. This is what creates the link or relationship. For example, an orders table would have an order_id as its primary key, but it would also have a customer_id column. This customer_id is a foreign key that points to the customer_id in the customers table, linking each order to the customer who made it.

This concept is fundamental to how relational databases work. Let's watch one more clip that explains this relationship between tables.

To visualize a more complex, real-world example, product teams often use Entity-Relationship Diagrams (ERDs). These are like blueprints for the database.

4. A Practical Example: Event Management

Let's solidify this by looking at how you might structure a database for an "event management" product. This is a great example of breaking down a complex domain into logical tables.

As you can see from that example, the process involves:

1. Identifying the core subjects (entities): Event, Attendee, Venue, etc. These become your tables.
2. Defining their properties (attributes): EVENT_NAME, ATTENDEE_ID, VENUE_NAME, etc. These become your columns.
3. Establishing relationships: Noticing that an attendee belongs to an event, so the Attendee table needs an EVENT_ID foreign key to link back to the Event table.

This way of thinking is the first and most critical skill in learning to work with data.

---

Conclusion

Great work today! We've laid the essential groundwork for the rest of this course.

Key Takeaways:

• Product concepts like users, products, and events are stored in a database.
• A database is organized into tables, where each table represents a single subject (e.g., users).
• Each table has columns that define its properties (e.g., email) and rows that represent individual records (e.g., one specific user).
• Tables are linked using primary keys (unique IDs) and foreign keys (references to other tables).
• Understanding this structure is the first step to asking the right questions of your data.

Next Lesson:
In our next session, we'll get hands-on and write our very first SQL query! We'll use the SELECT statement to start retrieving specific columns of data from a single table. Now that you know how the data is organized, you'll learn how to start exploring it.