Start with real things, not tables

The simplest way to begin is to name the real things you care about. In data modeling, these are often called entities. An entity is a thing you want to track, like a customer, an order, or a product.

Then list the details for each one. Those details are attributes. A customer might have a name and email. A product might have a price and SKU.

• Customer: customer_id, name, email
• Order: order_id, customer_id, order_date
• Product: product_id, name, price

This sounds basic, but it matters. If you skip this step, the data often turns into a pile of columns with no clear meaning.

The links that keep data clear

Each table needs a stable way to identify one row. That is the primary key. For example, customer_id can identify one customer.

When one table points to another, that link is a foreign key. An order can store customer_id so you know which customer placed it.

In a small shop example, the relationships look like this:

• One customer can have many orders.
• One order can contain many products.
• One product can appear in many orders.

Those links stop data from drifting apart. They help you answer simple questions like, “Which customer bought this?” without guessing.

Dive Deeper with BonsAI Chat

Why normalization helps

Normalization means organizing data so you do not repeat the same fact in many places. The goal is simple: store each fact once, in the right place.

For example, if you put the full customer name and email on every order row, you will repeat that data again and again. If the email changes, you now have many rows to fix. A normalized model keeps customer details in the customer table and lets orders point back to it.

Normalization usually helps when:

• the data changes often
• accuracy matters more than reporting speed
• many systems write to the same data

It is not about being fancy. It is about avoiding duplicate facts and update mistakes.

When a star schema makes more sense

Analytics has a different job. You usually want fast reads, clear metrics, and simple reporting. That is where a star schema often helps.

In a star schema, a central fact table stores events, like sales. Around it, dimension tables store context, like customer, product, and date.

• FactSales: order_id, product_id, customer_id, date_id, quantity, revenue
• DimCustomer: customer details
• DimProduct: product details
• DimDate: calendar fields for reporting

This model is often less normalized on purpose. It is built to answer questions like monthly revenue by product category, not to run the checkout system.

How to choose the right model

Use a more normalized model when you are building the system of record: the place where data is created, updated, and kept correct.

Use an analytics-friendly reporting model when the main job is reading, grouping, filtering, and measuring data fast.

• Choose normalized for app data, transactions, and operational workflows.
• Choose star schema for dashboards, BI tools, and trend analysis.
• Use both when needed: one model for clean storage, another for clear reporting.

If you are unsure, ask three simple questions: Where is the data born? What changes often? What questions do people need answered every day? The best model is the one that keeps those answers clear.