Full Stack Engineering · Day 3

Design data that stays correct, useful and fast.

A scrollable masterclass on relational modelling, transactions, backend integration, NoSQL awareness, time-series data and semantic vector search.

Requirements → ERDJava + PostgreSQLTransactions & ACIDORM + Connection PoolVector Search

Start with the workload, not the product.

Different databases optimize different jobs. A full-stack engineer should identify correctness rules, access patterns and growth before selecting technology.

Relational

Structured business data, relationships, constraints and multi-step transactions.

NoSQL

Document, key-value, wide-column and graph models for particular access patterns.

Vector & time series

Semantic similarity on one side; timestamped, append-heavy measurements on the other.

Workload	Example record	Natural fit
Order	9001 · customer 42 · paid · ₹79,999	Relational
Session	session:abc123 → customer 42 · expires 10:30	Key-value
API metric	10:15:30 · checkout · 184 ms	Time series
Semantic representation	product-101 → vector + metadata	Vector

Pragmatic default: keep one clearly identified system of record. Add specialized stores only when the workload earns the complexity.

Translate business language into a schema.

Seven-step database design journey from business requirements to indexes and performance

ShopSphere requirements

Customers maintain addresses.
Products belong to categories.
Orders contain products and reserve inventory.
Payment and stock must not partially update.
Natural-language product search is required.

Questions before tables

What must survive?
What rules must always be true?
What are the important reads and writes?
Which steps belong together?
How will volume grow?

Conceptual → logical → physical

Conceptual model: business entities and relationships, independent of a database product.

Logical model: tables, columns, primary keys, foreign keys and normalization.

Physical model: PostgreSQL types, indexes, partitions and storage decisions.

Write down invariants

An invariant is a condition that must remain true after every valid transaction.

Product price cannot be negative.
Inventory cannot fall below zero.
An order item must reference a real order and product.
A paid order must have an associated payment record.

Constraints protect structural invariants; transaction logic protects multi-step business invariants.

CUSTOMER

PK customer_id
email UNIQUE
full_name

1 places N

ORDER

PK order_id
FK customer_id
status
total_amount

1 contains N

ORDER_ITEM

PK order_id + product_id
quantity
unit_price

order_id · PK	customer_id · FK	status	total
9001	42	paid	₹79,999
9002	42	shipped	₹1,499
9003	43	pending	₹4,299

Rows 9001 and 9002 point to the same customer without duplicating that customer's email or name.

Read every relationship in both directions.

“One customer places many orders” also means “each order belongs to one customer.” The second sentence reveals where the foreign key belongs.

One-to-many · Customer → Orders

CUSTOMER

PK customer_id

1 : N

ORDER

PK order_id
FK customer_id

Rule: put the parent key on the many side.

One-to-one · Customer → Profile

CUSTOMER

PK customer_id

1 : 0..1

PROFILE

PK FK customer_id

Rule: shared primary key or a UNIQUE foreign key.

Many-to-many · Products ↔ Categories

PRODUCT

PK product_id

1 : N

PRODUCT_CATEGORY

PK FK product_id
PK FK category_id

N : 1

CATEGORY

PK category_id

Rule: resolve N:M through a junction table.

Optional · Order → Coupon

ORDER

PK order_id
FK coupon_id NULL

0..1

COUPON

PK coupon_id

Rule: nullable foreign key when absence is valid.

SQL · Junction table

CREATE TABLE product_categories (
  product_id  BIGINT REFERENCES products(product_id),
  category_id BIGINT REFERENCES categories(category_id),
  assigned_at TIMESTAMPTZ DEFAULT NOW(),
  PRIMARY KEY (product_id, category_id)
);

product_id	category_id	Meaning
101	10	TravelBook belongs to Laptops
101	20	TravelBook belongs to Travel Technology
205	10	GamePro belongs to Laptops

The composite primary key prevents product 101 from being assigned to category 10 twice.

Delete behaviour is part of design. Use CASCADE for disposable children, RESTRICT for protected history, and SET NULL only for optional links.

Five-question relationship test

How many B records can one A have?
How many A records can one B have?
Is either side optional?

Does the relationship carry its own data?
What should happen when either side is deleted?

Example: Enrollment is not merely a connector between Student and Course when it also stores enrollment date, status, completion and score. It is a business entity.

Give every fact one dependable home.

Normalization before and after showing one messy table transformed into customers, orders, order items and products

1NF

No repeating product columns. Store one order-item fact per row.

2NF

Every non-key fact depends on the entire key.

3NF

Customer facts belong to customers—not to orders.

PostgreSQL · Constraints protect every writer

CREATE TABLE products (
  product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku        VARCHAR(50) NOT NULL UNIQUE,
  name       VARCHAR(200) NOT NULL,
  price      NUMERIC(12,2) NOT NULL CHECK (price >= 0),
  status     VARCHAR(20) NOT NULL CHECK
             (status IN ('draft', 'active', 'discontinued'))
);

Attempted product row	Result	Rule
101 · LAP-14 · ₹79,999 · active	Accepted	All constraints satisfied
102 · LAP-14 · ₹69,999 · active	Rejected	Duplicate UNIQUE sku
103 · TAB-10 · ₹-500 · active	Rejected	CHECK price ≥ 0
104 · NULL · ₹25,000 · active	Rejected	NOT NULL sku

Normalized table	Example row	Fact represented
customers	42 · anita@example.com · Anita Rao	Who the customer is
orders	9001 · customer 42 · paid	Who placed the order
order_items	9001 · product 101 · quantity 1	What was purchased
order_items	9001 · product 205 · quantity 2	Another item in the same order

Choose precise types. Express changes safely with SQL.

A data type defines which values a column accepts and which operations are valid. SQL defines structures, changes rows, reads results and controls transactions.

Core PostgreSQL data types

Category	Common type	Column example	Example value
Whole number	INTEGER / BIGINT	quantity INTEGER	3
Exact decimal	NUMERIC(12,2)	price	79999.00
Short text	VARCHAR(n)	sku VARCHAR(50)	LAP-14
Long text	TEXT	description	Lightweight laptop…
Boolean	BOOLEAN	is_active	TRUE
Calendar date	DATE	date_of_birth	1992-04-18
Real-world instant	TIMESTAMPTZ	ordered_at	2026-08-19 10:15+05:30
Flexible structure	JSONB	specifications	{"ramGb":16}
Semantic representation	VECTOR(n)	embedding	[0.12,-0.44,…]

Money

Use Java BigDecimal and SQL NUMERIC. Binary floating point can introduce rounding artifacts.

Time

Use DATE for a calendar date and TIMESTAMPTZ for an instant viewed across time zones.

NULL

NULL means missing or unknown. It is different from zero and an empty string. Test it with IS NULL.

SQL command families

Family	Purpose	Commands
DDL	Define structures	CREATE, ALTER, DROP
DML	Create, change and remove rows	INSERT, UPDATE, DELETE
DQL	Read rows	SELECT
TCL	Control transactions	BEGIN, COMMIT, ROLLBACK
DCL	Control permissions	GRANT, REVOKE

CRUD in one view

Create · INSERT

INSERT INTO products (sku, name, price, status)
VALUES ('MOU-6', 'Wireless Mouse', 749.50, 'active');

Read · SELECT

SELECT product_id, name, price
FROM products
WHERE status = 'active'
ORDER BY price ASC
LIMIT 20;

Update · UPDATE

UPDATE products
SET price = 699.00
WHERE product_id = 102;

Delete · DELETE

DELETE FROM products
WHERE product_id = 103
  AND status = 'draft';

Always verify the WHERE clause before UPDATE or DELETE. Without it, every row may be changed or removed.

Filtering and NULL handling

SQL · Predicates

SELECT product_id, name, price
FROM products
WHERE status IN ('active', 'draft')
  AND price BETWEEN 500 AND 100000
  AND description IS NOT NULL;

Types of SQL joins

A join evaluates an ON condition to combine related rows. The join type decides what happens to rows that do not match.

Six-panel diagram comparing INNER, LEFT, RIGHT, FULL OUTER, CROSS and SELF joins using employee and department rows

employees

employee_id	name	department_id
1	Asha	10
2	Bilal	10
3	Chen	20
4	Divya	NULL

departments

department_id	department_name
10	Sales
20	Engineering
30	Finance

Join	Rows retained	Use it to answer
INNER	Matches only	Which employees have a department?
LEFT	All left + matches	Show every employee, including unassigned people.
RIGHT	All right + matches	Show every department, including empty ones.
FULL OUTER	All rows from both	Show every employee and department.
CROSS	Every left × right pair	Generate every possible combination.
SELF	Matches inside one table	Who manages each employee?

INNER JOIN · matches only

SQL

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d
  ON d.department_id = e.department_id;

Result: Asha–Sales, Bilal–Sales, Chen–Engineering. Divya and Finance are omitted.

LEFT JOIN · keep the left

SQL

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d
  ON d.department_id = e.department_id;

Result: the three matches plus Divya–NULL.

RIGHT JOIN · keep the right

SQL

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d
  ON d.department_id = e.department_id;

Result: the three matches plus NULL–Finance. Often written as a LEFT JOIN with table order reversed.

FULL OUTER · keep both

SQL

SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d
  ON d.department_id = e.department_id;

Result: all matches plus Divya–NULL and NULL–Finance. Support varies by SQL dialect.

CROSS JOIN · every combination

SQL

SELECT e.name, d.department_name
FROM employees e
CROSS JOIN departments d;

Result: 4 × 3 = 12 rows. A missing join condition can cause this row explosion accidentally.

SELF JOIN · employee → manager

SQL

SELECT e.name AS employee,
       m.name AS manager
FROM employees e
LEFT JOIN employees m
  ON m.employee_id = e.manager_id;

Result: Asha–NULL, Bilal–Asha, Chen–Asha, Divya–Bilal.

Reading rule: start with the left table. Must unmatched left rows remain? Must unmatched right rows remain? Those two decisions reveal the join type.

Aggregation

GROUP BY

SELECT status,
       COUNT(*) AS product_count,
       AVG(price) AS average_price
FROM products
GROUP BY status
ORDER BY status;

status	product_count	average_price
active	2	40374.25
draft	1	24999.00

Logical query order: FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

How backend code reaches the database.

The controller handles HTTP. The service owns business rules and the transaction. The repository performs data access. The pool manages scarce connections.

ControllerHTTP

→

Servicebusiness + transaction

→

Repositorydata access

→

ORM / JDBCmapping + SQL

→

Driverprotocol

→

Poolconnections

→

PostgreSQLtruth

JDBC driver

Authenticates, sends SQL and parameters, converts types, returns rows and reports database errors.

ORM

Maps Java objects and relationships to rows. It reduces CRUD boilerplate but does not remove the need to understand SQL.

Connection pool

Reuses a bounded set of connections. Calling close normally returns a borrowed connection to the pool.

HTTP Request

→

Borrow Connection

→

Run Transaction

→

Return Connection

Java · JPA entity

@Entity
@Table(name = "products")
public class Product {
  @Id @GeneratedValue
  private Long productId;

  @Column(nullable = false, unique = true)
  private String sku;

  private BigDecimal price;
}

Spring Boot · HikariCP

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/shopsphere
    username: app_user
    password: ${DB_PASSWORD}
    hikari:
      maximum-pool-size: 10
      minimum-idle: 2
      connection-timeout: 3000

An ORM is above JDBC; JDBC uses the database driver; both normally borrow connections from the same pool.

Connection pool state

Connection	State	Borrowed by	Transaction
C1	In use	POST /orders	TX-501
C2	In use	GET /products	Read query
C3	Available	—	—
C4	Available	—	—
C5	In use	Background job	TX-502

App instances	Pool per instance	Possible DB connections
1	10	10
5	10	50
10	20	200

Failure point	What the backend should do	Likely signal
Validation fails	Reject before borrowing a connection	400 Bad Request
Inventory update affects zero rows	Throw a business exception and roll back	409 Conflict
Foreign-key constraint fails	Roll back and map safely	400 or 404, by API design
Pool is exhausted	Wait only until configured timeout; alert	503 or controlled 500
Database is unavailable	Fail fast, avoid retry storms, expose health signal	503 Service Unavailable

ORM pitfalls worth demonstrating

N+1 queries: loading a list and lazily fetching one relationship per row.
Accidental eager graphs: one entity request loads far more data than intended.
Long persistence contexts: too many tracked objects increase memory and surprise updates.
Missing transaction boundary: multiple repository calls commit independently.
Assuming annotations replace constraints: the database still needs authoritative keys and checks.

One business action. One safe transaction.

ACID properties explained through an e-commerce order

Before transaction

Table	Visible state
inventory	product 101 · available 1
orders	No order 9006
order_items	No row for 9006

After commit

Table	Visible state
inventory	product 101 · available 0
orders	9006 · customer 42 · pending
order_items	9006 · product 101 · quantity 1

Atomicity

Order, stock and payment record succeed together—or roll back.

Consistency

Constraints and rules remain valid. Inventory cannot go negative.

Isolation

Two buyers cannot both purchase the final unit.

Durability

A committed order survives a system failure.

Java · JDBC transaction

try (Connection connection = dataSource.getConnection()) {
  connection.setAutoCommit(false);
  try {
    // Atomic reservation: check and reduce together
    try (PreparedStatement ps = connection.prepareStatement("""
      UPDATE inventory
         SET quantity_available = quantity_available - ?
       WHERE product_id = ? AND quantity_available >= ?
      """)) {
      ps.setInt(1, quantity);
      ps.setLong(2, productId);
      ps.setInt(3, quantity);
      if (ps.executeUpdate() != 1)
        throw new InsufficientInventoryException();
    }
    insertOrder(connection, customerId, productId, quantity);
    connection.commit();
  } catch (Exception error) {
    connection.rollback();
    throw error;
  } finally {
    connection.setAutoCommit(true);
  }
}

Key idea: begin, every SQL statement, commit and rollback must use the same Connection.

Do not hold a database transaction open while waiting for a remote payment provider. Use short local transactions, idempotency and a reliable event/outbox workflow.

Transaction step	If it fails before commit
Reserve inventory	No order exists; inventory update is rolled back
Create order	Inventory reservation is rolled back
Create order item	Order and inventory changes are rolled back
Commit	All successful changes become durable together

Make important queries fast—deliberately.

Without an index

The database may inspect every order to find one customer's history.

Row 1

Row 2

…

Row N

With a composite index

The database navigates directly to one customer's newest orders.

customer_id

→

ordered_at DESC

→

Top 20

SQL · Index and query plan

CREATE INDEX idx_orders_customer_date
  ON orders (customer_id, ordered_at DESC);

EXPLAIN ANALYZE SELECT * FROM orders
 WHERE customer_id = 42
 ORDER BY ordered_at DESC LIMIT 20;

Query	Usefulness of (customer_id, ordered_at)	Reason
customer 42 + newest first	Excellent	Matches both columns
customer 42 only	Useful	Uses leading column
date only	Usually poor	Skips leading customer_id
status=paid	None	Status is absent

Column order

Composite indexes follow query patterns. The leading column matters.

Write cost

Every index consumes storage and adds work to inserts and updates.

N+1

One list query plus one query per row is a backend access-pattern problem.

NoSQL is a family of models—not a shortcut.

Document model

Embed bounded data read together. Reference entities with independent lives or unbounded growth.

JSON · Product aggregate

{
  "id": "P101",
  "name": "TravelBook 14",
  "specifications": {
    "weightKg": 1.18, "ramGb": 16
  },
  "variants": ["silver", "blue"]
}

Time-series model

Timestamp + measurement + tags + fields. Designed for append-heavy metrics, telemetry and time-window aggregation.

Event · API latency

timestamp: 2026-08-19T10:15:30Z
measurement: api_request_duration
tags: service=checkout, region=ap-south-1
fields: duration_ms=184, status_code=201

Raw time-series rows

time	service	region	duration	status
10:15:00	checkout	ap-south-1	120 ms	201
10:15:10	checkout	ap-south-1	184 ms	201
10:15:20	checkout	ap-south-1	420 ms	500

A five-minute downsample can turn these into one row: checkout · requests 3 · average 241.3 ms · errors 1.

If the dominant need is…	Start by considering…	Example
Correct multi-row business operations	Relational	Orders and payments
Flexible bounded aggregates	Document	Variable catalogue specifications
Very fast key lookup	Key-value	Sessions and cache
Timestamp windows and retention	Time series	API latency and IoT readings
Semantic similarity	Vector	Natural-language product search

Search by meaning, then confirm current truth.

Product text

→

Embedding modelnumbers capture meaning

→

Vector index

←

Query embedding

←

Natural-language query

Embedding

A numeric representation where semantically related inputs tend to be close.

Top-K retrieval

Return the nearest candidate IDs using cosine, dot-product or Euclidean distance.

Metadata filters

Restrict results by tenant, permission, status, category or price band.

PostgreSQL · pgvector

SELECT p.product_id, p.name, p.price,
       1 - (pe.embedding <=> :query_embedding) AS similarity
  FROM product_embeddings pe
  JOIN products p ON p.product_id = pe.product_id
 WHERE p.status = 'active' AND p.price <= 100000
 ORDER BY pe.embedding <=> :query_embedding
 LIMIT 10;

Vector lifecycle

Read authoritative content.
Build searchable text or chunks.
Generate an embedding.
Store vector, stable ID and metadata.
Re-embed when source content changes.
Delete vectors when access or source records disappear.

Failure modes

Stale embeddings after content updates
Mixed embedding-model versions
Missing tenant or permission filter
Poor chunks with insufficient context
Assuming similarity means factual correctness
No representative relevance evaluation

vector_id	product_id	vector preview	status	version
product-101	101	[0.12, -0.44, 0.91, …]	active	product-v3
product-205	205	[0.08, 0.52, -0.17, …]	active	product-v3
product-310	310	[-0.33, 0.11, 0.62, …]	discontinued	product-v2

Vector search finds meaning. RDBMS confirms truth.

Relational and vector databases working together for semantic product search

Natural-language query

→

Vector searchcandidate IDs

→

PostgreSQLprice, stock, permissions

→

Final results

Keep authoritative price, inventory, payment and permissions in the relational system of record. Treat vectors as search representations.

PostgreSQL · source of truth

product	price	stock	status
101 · TravelBook 14	₹79,999	6	active

Vector index · search representation

vector	product	hash	indexed
product-101	101	a81f…	14:20:04

Outbox synchronization rows

outbox_id	event	product	status	attempts
771	product.updated	101	processed	1
772	product.updated	205	pending	0
773	product.deleted	310	retry	2

Apply the full design method.

Learning platform scenario

Learners enroll in courses, complete lessons, make payments, search lesson content semantically, and generate video-playback telemetry.

Identify entities and relationships.
Choose primary and foreign keys.
Add five integrity constraints.
Normalize to approximately 3NF.
Define the paid-enrollment transaction.

Propose two indexes.
Decide whether document NoSQL is justified.
Design a time-series playback event.
Define vector text and metadata.
Explain source-to-vector synchronization.

Reference solution

Core entities: User, Course, Module, Lesson, Enrollment, LessonProgress and Payment. Resolve users ↔ courses through Enrollment. Keep payment/enrollment changes transactional locally. Store playback telemetry by timestamp. Store lesson chunks and access-tier metadata in the vector index.

Reflection: If only one database could remain in this architecture, which would it be? The answer reveals the system of record.