Database Architecture · Beginner to job‑ready
Database Three-Tier Architecture Explained Simply
Three tiers, one sentence each
- Presentation tier (client): what the user sees and touches: a web page, a mobile app or a desktop screen. It collects input and displays results.
- Application tier (business logic, middle tier): a server program that validates requests, applies business rules, handles login and permissions, and decides what to ask the database.
- Data tier (database server): the DBMS, such as MySQL, PostgreSQL, Oracle or SQL Server, that stores data and runs queries and transactions.
The key rule: the presentation tier talks only to the application tier, and only the application tier talks to the database.
How one request flows
Take a student checking exam results on a university portal:
- The browser (presentation tier) sends
GET /results?roll=2301over HTTPS. - The application server checks the session: is this student logged in, and is roll number 2301 theirs?
- If yes, it runs a parameterised SQL query against the database.
- The database (data tier) returns rows for that roll number.
- The application tier calculates the SGPA, formats the data as JSON or HTML, and sends it back.
- The browser displays the result card.
The browser never holds a database password and never sends SQL. That single fact is where most of the security benefit comes from. The request/response style between the first two tiers is usually a REST API, explained in REST APIs with a simple example.
1-tier vs 2-tier vs 3-tier
| 1-tier | 2-tier (client–server) | 3-tier | |
|---|---|---|---|
| Where things run | UI, logic and database on one machine | Client app holds UI and logic; talks directly to a database server | Client, application server and database server are separate |
| Example | A desktop app with a local SQLite or Access file | A shop's billing software connecting straight to a central MySQL server | Most web and mobile applications |
| Security | Depends on the single machine | Each client needs database credentials | Only the application server has database access |
| Scalability | One user or a few | Limited by database connections from many clients | Application servers can be added behind a load balancer |
| Changing business rules | Reinstall the app | Update every client | Update the application server once |
A working three-tier example
Real systems put each tier on a different machine, but the separation is a design idea first. This Python program keeps the three tiers as separate classes and functions, with SQLite as the data tier:
import sqlite3
# ---------- Data tier: only knows how to store and fetch ----------
class OrderRepository:
def __init__(self):
self.db = sqlite3.connect(":memory:")
self.db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT, qty INTEGER)")
self.db.execute("CREATE TABLE stock (item TEXT PRIMARY KEY, available INTEGER)")
self.db.executemany("INSERT INTO stock VALUES (?, ?)", [("pen", 10), ("notebook", 2)])
def available(self, item):
row = self.db.execute("SELECT available FROM stock WHERE item = ?", (item,)).fetchone()
return row[0] if row else None
def save(self, item, qty):
self.db.execute("UPDATE stock SET available = available - ? WHERE item = ?", (qty, item))
cur = self.db.execute("INSERT INTO orders (item, qty) VALUES (?, ?)", (item, qty))
self.db.commit()
return cur.lastrowid
# ---------- Application tier: business rules ----------
class OrderService:
MAX_QTY = 5
def __init__(self, repo):
self.repo = repo
def place_order(self, item, qty):
if qty < 1 or qty > self.MAX_QTY:
return {"ok": False, "error": f"quantity must be 1-{self.MAX_QTY}"}
stock = self.repo.available(item)
if stock is None:
return {"ok": False, "error": "unknown item"}
if stock < qty:
return {"ok": False, "error": f"only {stock} {item} left"}
return {"ok": True, "order_id": self.repo.save(item, qty)}
# ---------- Presentation tier: formats input and output ----------
def show(result):
return f"Order #{result['order_id']} confirmed" if result["ok"] else f"Sorry: {result['error']}"
service = OrderService(OrderRepository())
for item, qty in [("pen", 3), ("notebook", 4), ("pen", 9), ("notebook'; DROP TABLE stock;--", 1)]:
print(f"{item!r:<36} x{qty}: {show(service.place_order(item, qty))}")
Output:
'pen' x3: Order #1 confirmed
'notebook' x4: Sorry: only 2 notebook left
'pen' x9: Sorry: quantity must be 1-5
"notebook'; DROP TABLE stock;--" x1: Sorry: unknown item
What to notice:
- Rules live in one place. The 1–5 quantity limit and the stock check are in
OrderService. If the limit changes to 10, nothing in the presentation or data code changes. - The data tier only stores and fetches.
OrderRepositoryknows SQL but nothing about business rules. - Parameterised queries stop SQL injection. The last input tries to smuggle
DROP TABLEinto the item name. Because the query uses?placeholders, the whole string is treated as a value, the lookup simply finds no such item, and the table survives. - The presentation tier only formats. Swapping
show()for a web page or a mobile screen would not touch the other two tiers.
Advantages
- Security: the database can sit on a private network that only application servers can reach.
- Scalability: add application servers when traffic grows; scale the database separately with replicas or a bigger machine.
- Maintainability: teams can change the UI, logic or database independently, as long as the interfaces between tiers stay the same.
- Reuse: one application tier can serve a website, an Android app and partner integrations through the same API.
Disadvantages
- More moving parts to deploy, monitor and secure.
- Extra network hops add some latency.
- For a small single-user tool, it can be more structure than the problem needs.
Common mistakes
- Business logic in the browser only: client-side checks can be bypassed; always validate again in the application tier.
- Building SQL with string concatenation: the classic route to SQL injection. Use placeholders or an ORM.
- Leaking database errors to users: raw error messages reveal table names; log them on the server and show a friendly message.
- Opening a new database connection per request: use a connection pool in the application tier.
Do not confuse it with these "three-level" ideas
Exam questions sometimes use similar names for different things:
- Three-schema (ANSI/SPARC) architecture of a DBMS: the external level (user views), conceptual level (logical structure of the whole database) and internal level (physical storage). It describes levels of abstraction inside the DBMS and gives logical and physical data independence. It is not about separate machines.
- Three-tier data warehouse architecture: a bottom tier (warehouse database), a middle tier (OLAP server) and a top tier (reporting and analysis tools). See 3-tier architecture in a data warehouse.
If a question says "three-tier client–server architecture", it means the presentation/application/data model on this page. If it says "three levels of DBMS architecture" or "data independence", it means ANSI/SPARC.
Where to go next
The data tier choice itself is a design decision; DBMS vs RDBMS and MongoDB vs MySQL cover the main options, and serializability in DBMS explains how the data tier keeps concurrent transactions correct.
Sources: Silberschatz, Korth and Sudarshan, Database System Concepts (application architectures; three-schema architecture); ANSI/X3/SPARC Study Group on DBMS, interim report (1975); OWASP SQL Injection Prevention Cheat Sheet