Database Architecture · Beginner to job‑ready

Database Three Tier Architecture Tutorial: understand the three layers and how they interact

SECTION 01What is three‑tier architecture?

The database three tier architecture (often called three tier architecture in dbms) is a client‑server model that divides an application into three distinct layers:

• Presentation tier – the user interface (web browser, mobile app, desktop client).
• Application tier – the business logic, rules, and processing (the “brain”).
• Data tier – the database management system that stores and retrieves data.

Each tier runs on its own infrastructure, often on separate physical or virtual machines. This separation allows teams to update or scale each tier independently, which is why most enterprise applications use a database 3 tier architecture.

SECTION 02Understanding each tier

1. Presentation Tier (Client)

This is what the user sees and interacts with. It sends user actions (clicks, form submissions) to the application tier and displays the results. It should contain no business logic – its only job is to present data and capture input.

• Examples: React/Vue web apps, Android/iOS apps, desktop GUIs.
• Communicates via: HTTP, REST, GraphQL, or WebSocket.
• Does NOT: connect directly to the database, or execute SQL.

2. Application Tier (Business Logic)

This is the heart of the system. It receives requests from the presentation tier, applies business rules, validates data, performs calculations, and then talks to the data tier. It also handles authentication, authorization, and logging.

• Examples: Node.js, Python/Django, Java Spring Boot, C# .NET.
• Communicates via: SQL, ORM (e.g., Entity Framework, Hibernate), or stored procedures.
• Does: enforce business logic, format data, manage sessions.

3. Data Tier (Database)

This tier stores and retrieves data. It can be a relational database (MySQL, PostgreSQL, SQL Server) or a NoSQL system (MongoDB, Cassandra). It ensures data integrity, provides indexes for performance, and handles concurrent access.

• Examples: PostgreSQL, Oracle, MongoDB, Redis.
• Communicates via: Database drivers, connection pools.
• Does: ACID transactions, query optimisation, replication.

SECTION 03Communication flow

A typical request flows as follows:

1. Client → Application: The user clicks a button (e.g., “Place Order”). The presentation tier sends an HTTP POST request with order details to the application server.
2. Application → Database: The application tier validates the input, checks stock availability, calculates the total, and then executes a SQL INSERT or uses an ORM to persist the order. It may also query the database for product prices and customer data.
3. Database → Application: The database returns a success status and the new order ID.
4. Application → Client: The application tier formats a response (e.g., JSON with order confirmation) and sends it back to the client.

This separation ensures that the database is never exposed directly to the client, which is a fundamental security principle of the three tier architecture in dbms.

// Express.js endpoint - Application tier
app.post('/api/orders', async (req, res) => {
  try {
    const { productId, quantity, userId } = req.body;
    if (!productId || quantity < 1) {
      return res.status(400).json({ error: 'Invalid input' });
    }
    const product = await db.query('SELECT price, stock FROM products WHERE id = $1', [productId]);
    if (product.stock < quantity) {
      return res.status(409).json({ error: 'Insufficient stock' });
    }
    const total = product.price * quantity;
    const order = await db.query(
      'INSERT INTO orders (user_id, total, status) VALUES ($1, $2, $3) RETURNING id',
      [userId, total, 'PENDING']
    );
    await db.query('UPDATE products SET stock = stock - $1 WHERE id = $2', [quantity, productId]);
    res.status(201).json({ orderId: order.id, total });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

# Django view - Application tier
from django.http import JsonResponse
from django.db import transaction
from .models import Product, Order, OrderItem

def place_order(request):
    if request.method != 'POST':
        return JsonResponse({'error': 'Method not allowed'}, status=405)
    
    product_id = request.POST.get('product_id')
    quantity = int(request.POST.get('quantity', 0))
    user = request.user
    
    if not product_id or quantity < 1:
        return JsonResponse({'error': 'Invalid product or quantity'}, status=400)
    
    try:
        with transaction.atomic():
            product = Product.objects.select_for_update().get(id=product_id)
            if product.stock < quantity:
                return JsonResponse({'error': 'Stock insufficient'}, status=409)
            total = product.price * quantity
            order = Order.objects.create(user=user, total=total, status='PENDING')
            OrderItem.objects.create(order=order, product=product, quantity=quantity, price=product.price)
            product.stock -= quantity
            product.save()
            return JsonResponse({'order_id': order.id, 'total': total})
    except Product.DoesNotExist:
        return JsonResponse({'error': 'Product not found'}, status=404)

-- Data tier: Stored procedure for placing an order (example in PL/pgSQL)
CREATE OR REPLACE FUNCTION place_order(
    p_user_id INT,
    p_product_id INT,
    p_quantity INT
) RETURNS TABLE (order_id INT, total DECIMAL) AS $$
DECLARE
    v_price DECIMAL;
    v_stock INT;
    v_order_id INT;
    v_total DECIMAL;
BEGIN
    SELECT price, stock INTO v_price, v_stock FROM products WHERE id = p_product_id FOR UPDATE;
    IF v_stock < p_quantity THEN
        RAISE EXCEPTION 'Insufficient stock';
    END IF;
    v_total := v_price * p_quantity;
    INSERT INTO orders (user_id, total, status) VALUES (p_user_id, v_total, 'PENDING') RETURNING id INTO v_order_id;
    INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (v_order_id, p_product_id, p_quantity, v_price);
    UPDATE products SET stock = stock - p_quantity WHERE id = p_product_id;
    RETURN QUERY SELECT v_order_id, v_total;
END;
$$ LANGUAGE plpgsql;

SECTION 04Advantages over two‑tier architecture

SECTION 05Real‑world project: e‑commerce system

Let's apply this knowledge to build a simplified e‑commerce backend. We'll design a three‑tier architecture for a bookstore.

1. Presentation Tier: A React SPA with pages for browsing books, adding to cart, and checkout. It makes REST calls to the app tier.
2. Application Tier: A Node.js/Express server that handles user authentication, book search, cart management, and order processing. It uses an ORM to talk to the database.
3. Data Tier: A PostgreSQL database with tables for users, books, carts, orders, and order_items. The app tier executes parameterised queries to prevent SQL injection.
4. Communication: The presentation tier sends AJAX requests to the app tier; the app tier uses a connection pool to talk to the database.
5. Security: JWT tokens are issued by the app tier; the database never sees raw user credentials.
6. Scaling: When traffic spikes, we add more app server instances behind a load balancer. The database is scaled with read replicas for reporting queries.

Stretch goal: Add a caching tier (Redis) between the app and data tiers to reduce database load.

SECTION 06Common errors & fixes

SECTION 07Interview questions with model answers

SECTION 08Test yourself

SECTION 09Frequently asked questions

SECTION 10Continue from here