Customers and Orders

basic

One customer can place many orders without duplicating customer details in every order row.

CUSTOMERS TABLE:

customer_id | first_name | last_name | email

1 | John | Smith | john@example.com

2 | Jane | Doe | jane@example.com

ORDERS TABLE:

order_id | customer_id | order_date | total

1001 | 1 | 2024-01-15 | 150.00

1002 | 1 | 2024-01-20 | 200.00

1003 | 2 | 2024-01-18 | 75.00

Customer data is stored once and ORDERS points back to it through customer_id.

Separate entities stay connected through a key relationship instead of duplicated text.

All

Why Normalization Matters

basic

Compare storing everything in one table versus splitting data into related tables.

BAD APPROACH:

order_id | customer_name | customer_email | product_name | quantity | price

1001 | John Smith | john@example.com | Laptop | 1 | 999.99

1001 | John Smith | john@example.com | Coffee Maker | 1 | 79.99

BETTER APPROACH:

CUSTOMERS(customer_id, name, email)

ORDERS(order_id, customer_id, order_date)

ORDER_ITEMS(order_id, product_id, quantity, unit_price)

Customer information no longer needs to be updated in multiple places.

Normalization removes redundancy first, then makes integrity and later queries easier to trust.

All

Foreign Keys Protect Relationships

intermediate

An order should never exist for a customer that is not present in the customer table.

CUSTOMERS(customer_id PK, name, email)

ORDERS(order_id PK, customer_id FK, order_date, status)

Foreign key rule:

orders.customer_id -> customers.customer_id

Invalid child rows are blocked and the relationship stays trustworthy.

Foreign keys prevent orphaned rows and keep joins dependable.

All

ACID in a Money Transfer