Database Architecture and Data Models

Introduction

A database is an organized collection of data managed so that information can be stored, retrieved, updated, protected, and shared efficiently. Database architecture describes how database components are arranged and how users, applications, servers, and data communicate. A data model describes how data is represented and how relationships among data items are expressed.

These ideas are foundational for full-stack programming. A web application may use a browser or mobile client, an application server, and one or more databases. Choosing an unsuitable architecture or data model can cause slow performance, excessive complexity, security weaknesses, and difficult maintenance. Choosing appropriately can improve scalability, reliability, development speed, and data quality.

This chapter compares centralized, client-server, three-tier, and distributed architectures, followed by relational and major NoSQL models: document, key-value, column-family, and graph databases.

Learning Objectives

By the end of this chapter, you should be able to:

• Explain the purpose of database architecture and data models.
• Compare centralized, client-server, three-tier, and distributed architectures.
• Describe the structure and uses of relational, document, key-value, column-family, and graph models.
• Select an appropriate architecture and model for a practical application.
• Identify trade-offs involving consistency, scalability, security, flexibility, and maintainability.

1. Architecture and Data Models

Architecture concerns the placement and interaction of system components. It answers questions such as:

• Where is data stored?
• Where is processing performed?
• How do clients communicate with the database?
• What happens when a server fails?
• How can the system grow as users and data increase?

A data model provides concepts for describing data. It defines entities or records, attributes, relationships, constraints, and operations. Architecture and model are related but not identical. A three-tier application may use a relational database, a document database, or several different databases. Similarly, a relational database may be deployed on a single server or across a distributed cluster.

Important quality dimensions include:

• Performance: speed and throughput of operations.
• Scalability: ability to handle increasing workload, often by adding resources.
• Availability: ability to remain accessible when components fail.
• Consistency: whether users observe a coherent and current state of data.
• Security: protection against unauthorized access or alteration.
• Maintainability: ease of modifying, testing, and operating the system.
• Data integrity: correctness and validity of stored data.

2. Centralized Database Architecture

In a centralized architecture, the database and most database-management functions operate on one central computer or server. Users or applications send requests to this central system.

A simple arrangement is:

Users → Central database server → Storage

The central server controls data access, transactions, security, backup, and recovery. Early mainframe systems commonly used this architecture, although modern cloud systems may also use a single primary database server.

Advantages

Centralization provides a single authoritative copy of data. Administration, backup, auditing, and security policies are relatively straightforward. Transactions can be coordinated within one database engine, and there is little need for synchronization among database copies.

Limitations

The central server may become a bottleneck or a single point of failure. Network interruption can prevent all users from accessing the database. As the number of users increases, upgrading one server may become expensive. A centralized design may also create geographical latency for users located far from the server.

Suitable uses

Centralized architecture is appropriate for small organizations, classroom systems, local applications, and workloads whose size and availability requirements are modest. It can also be a useful starting point before introducing replication or distribution.

3. Client-Server Architecture

In client-server architecture, client programs request database services from a database server. The client may contain the user interface and some application logic, while the server manages data storage, query processing, transactions, and access control.

Client application ⇄ Database server

A traditional two-tier application is a common example. A desktop program connects directly to a database such as PostgreSQL, MySQL, or Microsoft SQL Server.

Benefits

The server provides centralized data management and can serve many clients. Database engines optimize queries and enforce permissions. Compared with a fully centralized terminal system, clients can provide richer interfaces and local processing.

Problems

Direct connections from many clients can consume database connections and complicate security. Business rules may be duplicated across client applications. Updating the application may require updating many devices. Exposing a database directly to the public internet is especially risky because clients may attempt unauthorized queries or exploit weak credentials.

Client-server architecture remains useful for controlled internal networks, desktop systems, and small business applications. For internet-facing systems, a three-tier design is generally safer and easier to maintain.

4. Three-Tier Architecture

Three-tier architecture separates an application into three logical layers:

1. Presentation tier: web browser, mobile interface, or desktop user interface.
2. Application tier: business rules, authentication, validation, APIs, and transaction coordination.
3. Data tier: database-management system and persistent storage.

The flow is generally:

Client → Application server/API → Database

The client does not normally connect directly to the database. Instead, it sends an HTTP request to an API. The application server validates the request, applies business rules, performs a parameterized database operation, and returns an appropriate response.

Advantages

Three-tier architecture improves security because database credentials and internal structure remain behind the application layer. It supports separation of concerns: interface developers, application developers, and database administrators can work on distinct responsibilities. Business rules are centralized, making them easier to test and update. Connection pooling allows a controlled number of database connections to serve many users.

The architecture also supports multiple clients. A web browser, mobile application, and administrative dashboard can use the same API without duplicating business rules.

Limitations

Each request passes through additional software layers, which may introduce latency and operational complexity. The application tier can become a bottleneck if it is poorly designed. Developers must manage API design, authentication, logging, deployment, and failure handling.

Practical example

In an online bookstore, the presentation tier displays books and shopping carts. The application tier checks stock, calculates prices, validates payments, and creates orders. The data tier stores customers, books, inventory, and order records. The client should not be permitted to update inventory directly; only authorized application operations should perform that action.

5. Distributed Database Architecture

A distributed database stores data across multiple networked computers while presenting the data as one logical system. Distribution may involve replication, partitioning, or both.

• Replication stores copies of the same data at multiple locations.
• Partitioning, also called sharding, divides a dataset into portions stored at different locations.
• Horizontal partitioning divides rows or documents.
• Vertical partitioning divides columns or attributes.

A distributed database may have several nodes in one data center or across geographical regions.

Advantages

Distribution can improve availability because another node may continue operating after a failure. It can reduce latency by placing data near users. Partitioning allows workload growth across multiple machines. Replication can support read-heavy applications and disaster recovery.

Challenges

Distributed systems must coordinate over networks, which can fail or become slow. Maintaining consistency among copies is difficult. Operations involving several nodes may require distributed transactions, increasing complexity. Monitoring, backup, schema changes, security, and troubleshooting also become more demanding.

A useful distinction is between horizontal scaling and vertical scaling. Vertical scaling increases the capacity of one machine, for example by adding memory or processors. Horizontal scaling adds machines. Horizontal scaling can support greater capacity but usually requires careful partitioning and coordination.

Consistency and availability trade-offs

When a network partition occurs, a distributed system may have to choose whether to reject some operations until consistency is restored or continue accepting operations with temporarily different views of data. Systems designed for strong consistency prioritize a current, coordinated view. Systems designed for eventual consistency may accept updates and reconcile replicas later. Neither approach is universally superior; the correct choice depends on the application.

For example, a social-media “like” count may tolerate temporary differences between regions, while a bank balance generally requires strict transactional controls.

6. Relational Data Model

The relational model represents data in relations, commonly implemented as tables. A table consists of rows and columns. Each row represents a record or tuple, and each column represents an attribute with a defined domain.

For example, a simplified Student table may contain:

A primary key uniquely identifies each row. A foreign key refers to a key in another table and represents a relationship. A database may contain Student, Course, and Enrollment tables, with Enrollment connecting students and courses.

Relational principles

The relational model emphasizes structured schemas, declarative queries, and integrity constraints. SQL is used to define, manipulate, and query relational data. Common constraints include:

• NOT NULL, requiring a value.
• UNIQUE, preventing duplicate values.
• CHECK, restricting values according to a condition.
• Primary-key constraints.
• Foreign-key constraints.

Normalization

Normalization organizes tables to reduce unnecessary duplication and update anomalies. For example, storing a customer’s address repeatedly in every order can cause inconsistent addresses when the customer moves. Separating customers and orders reduces this problem.

Normalization is not an absolute rule. Highly normalized designs may require more joins, so carefully selected denormalization can improve read performance in reporting or high-volume systems. Any denormalization should be documented and supported by integrity controls.

Strengths and limitations

Relational databases are strong when data has stable structure, relationships, and transactional requirements. They provide mature tools, SQL standards, constraints, indexing, backup, and transaction support. They may be less convenient when records vary greatly in structure, when rapid schema evolution is essential, or when data consists primarily of deeply connected relationships that require many joins.

7. Document Data Model

A document database stores records as documents, commonly represented using JSON-like structures. A document may contain nested objects and arrays.

Example:

{
  "customerId": 101,
  "name": "Amina Yusuf",
  "addresses": [
    {"city": "Kano", "type": "home"}
  ],
  "preferences": {"language": "English"}
}

Documents in the same collection need not have exactly the same fields. This is often called schema flexibility, although applications still need validation rules and a clear data design.

Strengths

Documents are convenient for application objects, content management, product catalogs, user profiles, and event records. Related information can be embedded so that one read retrieves a complete aggregate. This can reduce joins and simplify application code.

Limitations

Duplicating embedded information can create update problems. Cross-document transactions and complex relationships may be less natural than in relational systems. Flexible schemas can lead to inconsistent records if validation is neglected. Document databases are therefore not automatically “schema-free”; they usually move some schema responsibility from the database to the application or validation layer.

8. Key-Value Data Model

A key-value database stores a value identified by a unique key:

key → value

The value may be text, a number, binary data, or a structured object, depending on the system.

Example:

session:8f31 → {userId: 101, expires: ...}

Operations commonly include retrieving, inserting, updating, and deleting a value by key.

Uses and trade-offs

Key-value stores are effective for caching, sessions, feature flags, shopping carts, counters, and simple lookup services. They can provide very fast access and easy horizontal scaling. However, they generally offer limited querying by value and fewer relationship operations. If an application frequently asks questions such as “find all customers in a region with unpaid invoices,” a key-value model may be unsuitable unless additional indexes or services are introduced.

9. Column-Family Data Model

Column-family databases, sometimes called wide-column databases, organize data by row keys and flexible groups of columns. Different rows may contain different columns. They are designed for very large datasets and distributed workloads.

A sensor system might use a row key such as device-17:2025-01-01 and store timestamped readings in columns or cells. A common design begins with the queries the system must support and chooses row keys and partition keys accordingly.

Strengths

Column-family systems can provide high write throughput, large-scale storage, and predictable access when queries are known in advance. They are useful for time-series data, logging, telemetry, and high-volume event processing.

Limitations

They are usually less suitable for ad hoc queries and complex joins. Data is often deliberately duplicated to support specific query patterns. Poor partition-key design can create “hot spots,” in which too much traffic is directed to one node.

10. Graph Data Model

A graph database represents data as nodes, relationships, and properties. A node may represent a person, product, or location; a relationship may represent “follows,” “purchased,” or “connected to.”

Example:

(Amina)-[PURCHASED]->(Database Book)

Relationships are first-class data items. This makes graph databases effective for social networks, recommendation systems, fraud detection, knowledge graphs, route planning, and dependency analysis.

Strengths and limitations

Graph databases perform well for traversals across connected data. They can express changing relationship types naturally and make relationship-oriented queries readable. However, they may not be the best choice for simple tabular reporting, large-scale uniform aggregation, or systems whose data is mostly independent records. Graph systems also require developers to understand graph query patterns and traversal performance.

11. Comparing the Models

The term polyglot persistence describes using more than one database technology in an application. For example, an online store might use a relational database for orders, a key-value store for sessions, a search engine for text search, and a graph database for recommendations. Polyglot persistence can match tools to workloads, but it increases operational and data-consistency complexity.

12. Selecting an Architecture and Model

A practical selection process should begin with requirements rather than technology popularity.

1. Identify the main data entities and relationships.
2. List the most important read and write operations.
3. Estimate data volume, request rate, and growth.
4. Determine transaction and consistency requirements.
5. Identify availability, latency, and geographical needs.
6. Evaluate security, compliance, backup, and recovery requirements.
7. Consider team expertise, cost, tooling, and operational support.
8. Test realistic workloads with a prototype.

A relational database is often the safest default for a new business application with structured data and transactions. A document model may be appropriate when records are aggregate-oriented and vary in structure. A key-value model is suitable for simple lookup. A column-family model suits very large, predictable, distributed workloads. A graph model is appropriate when relationships and traversals are central to the application.

Applied Case: Regional Healthcare Appointment System

Background

This illustrative case concerns a healthcare organization operating clinics in three cities. Patients book appointments through a web and mobile application. Doctors manage schedules, and administrators produce reports.

Problem

The organization’s original single-server application experienced slow access during peak registration periods. The database contained patients, appointments, clinicians, payments, and medical documents. The system also exposed database credentials in a desktop client, creating a security risk.

Analysis

Appointments and payments require reliable transactions. A patient should not receive two appointments for the same doctor and time slot. Patient profiles contain structured core information but may include variable preferences. Medical documents are large objects requiring separate access controls. Reporting requires consistent, structured records.

Approach and solution

The organization adopted a three-tier architecture. Web and mobile clients communicate with an authenticated API. The application tier validates input, enforces authorization, and coordinates transactions. A relational database stores patients, clinicians, appointment slots, invoices, and payments. Unique constraints prevent duplicate appointment bookings.

A key-value store is used for short-lived login sessions and frequently accessed availability summaries. Documents are stored in protected object storage, while metadata remains in the relational database. Read replicas support reporting and geographically distributed access, but appointment creation is directed to the authoritative transactional database.

Outcome

The design removes direct database access from clients, centralizes business rules, and separates transactional data from temporary session data. Replicas improve read capacity, while backups and tested recovery procedures improve resilience. The system remains more complex than the original application, so monitoring, access reviews, and data reconciliation are required.

Learning points

The case demonstrates that architecture and data model decisions should be made per workload. One database technology need not perform every task. Strong consistency is essential for booking and payment operations, while temporary session data can use a faster key-value model. Security and recovery are architectural requirements, not optional additions.

Governance, Security, and Ethical Considerations

Database design affects privacy and fairness. Organizations should collect only necessary personal data, define retention periods, and restrict access according to job responsibility. Sensitive information should be encrypted in transit and, where appropriate, at rest. Passwords must never be stored as plain text; password-verification systems should use strong, salted password-hashing algorithms.

Applications should use parameterized queries or safe database libraries to reduce injection risks. Administrative actions should be logged, and logs should avoid unnecessary sensitive data. Backups require access controls and encryption because a backup is also a copy of the data.

Regulatory obligations vary by jurisdiction and sector. Healthcare, finance, education, and public-sector systems may be subject to specific privacy, retention, audit, and breach-notification requirements. Data models should support correction, deletion, access review, and data-subject requests where applicable.

Artificial intelligence may assist with schema suggestions, query optimization, anomaly detection, or natural-language interfaces. However, generated SQL and automated recommendations must be reviewed. AI tools may expose confidential data through prompts or logs, and automated decisions based on incomplete data can cause harm. Human oversight, testing, access control, and clear accountability remain necessary.

Best Practices

• Start with business requirements and access patterns.
• Use a relational model when integrity and transactions dominate.
• Design NoSQL schemas around actual queries rather than abstract entities alone.
• Separate presentation, business, and data responsibilities.
• Avoid direct public access to production databases.
• Use least privilege and parameterized queries.
• Define backup, restoration, and disaster-recovery procedures before deployment.
• Monitor latency, errors, storage growth, replication lag, and capacity.
• Document intentional denormalization and duplicated data.
• Test realistic workloads and failure scenarios.
• Review privacy, retention, and access requirements throughout the system life cycle.

Chapter Summary

Database architecture determines how clients, application services, database servers, and storage interact. Centralized systems are simple but may have bottlenecks and single points of failure. Client-server systems separate clients from database services, while three-tier systems add an application layer that improves security, maintainability, and control. Distributed databases improve scalability, availability, and geographical performance but introduce coordination and consistency challenges.

The relational model represents structured data in tables and is strong for constraints, SQL, and transactions. Document databases represent flexible, nested records; key-value databases provide fast lookup by key; column-family databases support high-volume distributed workloads; and graph databases model connected entities and relationships. No model is universally best. Selection should reflect data structure, access patterns, consistency needs, scale, security, operational capability, and cost.

Key Terms

Architecture: The arrangement and interaction of components in a database system.

Centralized database: A database managed primarily on one central computer or server.

Client-server architecture: An arrangement in which clients request services from a database server.

Three-tier architecture: Separation into presentation, application, and data tiers.

Distributed database: A logically unified database whose data or processing is distributed across networked nodes.

Replication: Maintaining copies of data at multiple locations.

Partitioning: Dividing data into portions stored or processed separately.

Scalability: The ability to handle increased workload by adding resources.

Consistency: The degree to which users observe a coherent and valid data state.

Relational model: A model based on tables, rows, columns, keys, and relationships.

Primary key: An attribute or set of attributes that uniquely identifies a row.

Foreign key: An attribute that refers to a key in another table.

Normalization: Organizing data to reduce duplication and update anomalies.

Document database: A database that stores self-contained, JSON-like documents.

Key-value database: A database that associates each unique key with a value.

Column-family database: A distributed model using row keys and flexible groups of columns.

Graph database: A database representing nodes, relationships, and properties.

Polyglot persistence: Using multiple database technologies within one application.

Eventual consistency: A model in which replicas may temporarily differ but converge after updates propagate.

Least privilege: Giving users and services only the permissions they require.

Review Questions

Short Answer Questions

1. What is the difference between database architecture and a data model?
2. State two advantages and two limitations of centralized architecture.
3. What are the three tiers in a three-tier application?
4. Distinguish replication from partitioning.
5. Why are primary keys and foreign keys important in relational databases?

Long Answer Questions

1. Compare client-server and three-tier architectures in terms of security, maintenance, and performance.
2. Explain the strengths and limitations of the relational, document, and key-value models.
3. Describe the main challenges introduced by distributed databases.
4. Explain normalization and discuss why an organization might deliberately denormalize some data.
5. Compare column-family and graph databases, including suitable application areas for each.

Analytical / Application Questions

1. Design an architecture for an online examination system. Justify your choice of tiers, database model, consistency requirements, and security controls.
2. A social-network application must support user profiles, rapid session lookup, relationship traversal, and financial subscriptions. Recommend suitable database models for these workloads and explain the trade-offs.
3. A company plans to move a centralized inventory database to multiple regions. Analyze the benefits, risks, consistency requirements, partitioning strategy, and recovery procedures that should be considered.

Multiple Choice Questions with Answers

Question 1

Which layer in a three-tier architecture normally applies business rules and exposes APIs?

A. Presentation tier

B. Application tier

C. Data tier

D. Storage device

Correct Answer: B. Application tier

Question 2

What is the primary purpose of a foreign key?

A. To encrypt a table

B. To sort all rows automatically

C. To reference a key in another table

D. To remove duplicate databases

Correct Answer: C. To reference a key in another table

Question 3

Which model is most naturally suited to direct lookup using a unique identifier?

A. Key-value

B. Graph

C. Relational

D. Column-family

Correct Answer: A. Key-value

Question 4

Which database model represents nodes and relationships as first-class elements?

A. Document

B. Key-value

C. Graph

D. Relational

Correct Answer: C. Graph

Question 5

What does replication mean in a distributed database?

A. Deleting old records

B. Maintaining copies of data at multiple locations

C. Dividing one column into smaller columns

D. Converting tables into documents

Correct Answer: B. Maintaining copies of data at multiple locations

Question 6

Which situation most strongly favors a relational database?

A. Simple temporary session lookup

B. Highly connected social relationships

C. Financial transactions requiring integrity constraints

D. Unstructured logs with predictable partition keys

Correct Answer: C. Financial transactions requiring integrity constraints

Question 7

What is a major risk of embedding duplicated information in documents?

A. The database cannot store text

B. Updates may produce inconsistent copies

C. Keys become impossible to generate

D. All documents must have identical fields

Correct Answer: B. Updates may produce inconsistent copies

Question 8

Which architecture prevents ordinary public clients from connecting directly to the database?

A. Three-tier architecture

B. Single-file architecture

C. Centralized terminal architecture only

D. Unmanaged peer storage

Correct Answer: A. Three-tier architecture

Question 9

Which workload is especially suitable for a column-family database?

A. Complex relationship traversal among people

B. High-volume telemetry with known access patterns

C. A small relational enrollment system

D. Password hashing alone

Correct Answer: B. High-volume telemetry with known access patterns

Question 10

What is the principle of least privilege?

A. Giving every user administrator access

B. Allowing only the permissions required for a task

C. Storing all data in one server

D. Replicating every record globally

Correct Answer: B. Allowing only the permissions required for a task