Overview of the Table Structure

When a new data request comes in, like analyzing top-selling products or identifying at-risk customers, our ability to respond quickly depends on understanding our database’s structure.

Think of it as the blueprint of a house. Without knowing which room leads where, we’ll wander around trying to make sense of things. In this lesson, we’ll explore the structure of the OnlineStore database, which we’ll use throughout the course.

We’ll walk through each key table, understand what information they hold, and see how they connect. This knowledge sets us up to apply SQL patterns with accuracy and intention.

By the end of this lesson, we will:

• Understand the purpose and structure of each core table in the database.

• Learn how the tables are related through keys and relationships.

Categories and Products

These two tables define the backbone of what the store offers and how each item is organized.

The Categories table contains high-level groupings such as 'Books', 'Electronics', or 'Clothing'. Each category is uniquely identified by a CategoryID.

The Products table holds individual product details, including ProductName, Price, Stock, and MonthlySales. Each product is linked to its category using the CategoryID foreign key.

This relationship is essential when grouping products for summaries or aggregations, such as total stock per category or average price by category.

Customers

The Customers table stores data about the people who shop in our store. It includes their contact info, loyalty level, and activity indicators.

Important columns:

• CustomerTier: tells us if someone is a 'New', 'Regular', or 'VIP' customer.

• LoyaltyPoints: a numeric representation of how much a customer has engaged with the store.

• IsChurnRisk: a boolean flag that helps us identify customers at risk of becoming inactive.

There’s also a self-referencing foreign key: ReferralID, which points to another CustomerID in the same table. This means ReferralID stores the customer's ID who referred the current customer, so we can track which customer invited or referred another.

This table is frequently used in filtering, ranking, and comparison-based query patterns.

Orders and Order_Details

These two tables track what customers buy.

• The Orders table records the main order transaction, such as OrderDate, TotalAmount, DeliveryStatus, and whether the order is considered a FraudRisk or was a LateDelivery—both generated columns.

• The Order_Details table breaks each order down into individual items. Each row here tells us which ProductID was ordered, how many units were bought (Quantity), and the TotalItemPrice.

These tables are ideal for exploring patterns like tally counts, group aggregations, or order-based comparisons.

Suppliers and Product_Suppliers

Not all products come from a single supplier. That’s where this pair of tables helps.

• Suppliers hold company details like SupplierName, OnTimeDeliveryRate, and AvgLeadTime.

• Product_Suppliers is a join table that maps which suppliers provide which products. This enables a many-to-many relationship.

Understanding these tables becomes useful when analyzing supply chain performance, identifying the best-performing suppliers, or evaluating sourcing diversity.

Relationships and table connections

Here’s a quick overview of how the key tables relate:

• Products→Categories: many-to-one (each product has one category)

• Orders→Customers: many-to-one (each order has one customer)

• Order_Details→Orders and Products: many-to-one in both directions

• Product_Suppliers→Products and Suppliers: many-to-many

• Customers→Customers: self-join via ReferralID

These relationships make SQL joins possible and allow us to write powerful queries that combine information from multiple sources.