Database Design for Data Engineering: A Practical Guide


Database design is the work of turning the information an application needs into a structure that can store, protect, retrieve, and evolve data reliably. It is more than drawing tables or choosing a database product. Good design starts with the people and processes the system supports, identifies the facts that must be recorded, defines how those facts relate, and anticipates the queries and transactions the application must perform.
For data engineering systems, these decisions influence every stage of the data lifecycle: ingestion, validation, transformation, storage, integration, analytics, governance, and delivery. A poorly designed schema can create duplicate records, inconsistent metrics, fragile pipelines, expensive queries, and migrations that become harder with every release. A thoughtful design gives engineers a dependable foundation while leaving room for new sources, changing workloads, and growing volumes.
This guide explains a practical database design process, from requirements and entity modelling to relational schemas, normalization, dimensional models, document databases, indexing, partitioning, data governance, and production operations. It also works through a data engineering example and provides a checklist you can use before implementing a database.
Database design is the process of deciding how data will be represented and managed in a database. It includes the logical structure of information, the physical choices that affect storage and performance, and the rules that preserve correctness as data changes. A design describes what the system stores, how records connect, which values are valid, how applications access the data, and how the structure can evolve.
A useful way to understand the work is to separate three related levels.
Conceptual design describes the business or operational domain without committing to a particular database engine. It identifies the important entities, the facts the organization needs to remember, and the relationships between them. A retail platform, for example, may need customers, products, orders, payments, shipments, and returns.
The conceptual model should make sense to both technical and non-technical stakeholders. It answers questions such as: What counts as an order? Can an order contain several products? Can one payment cover multiple orders? Does a customer account represent a person, an organization, or either? Resolving these questions early prevents the database from encoding conflicting assumptions.
Logical design turns the conceptual model into a structured data model. In a relational database, this usually means defining tables, columns, primary keys, foreign keys, constraints, and relationships. It also includes decisions about normalization and the handling of optional or repeating information.
The logical model should be independent of avoidable implementation details. It expresses the rules of the domain clearly enough that the team can evaluate correctness before choosing specific storage settings or tuning a particular database engine.
Physical design adapts the logical model to the selected platform and workload. It includes indexes, partitioning, clustering, storage formats, compression, replication, and other engine-specific choices. Physical design affects how efficiently the database executes queries and writes, but it should support a sound logical model rather than compensate for unclear requirements.
These levels are connected. A change in the business definition of an order may require a logical schema change, which in turn may require a migration and new indexes. Treating database design as a one-time diagramming task misses this ongoing responsibility.
Data engineering systems move information between operational applications, message brokers, object storage, warehouses, and analytical services. Each boundary introduces decisions about identifiers, timestamps, data types, ownership, update behavior, and historical retention.
A reliable design makes those decisions explicit. It helps teams identify where a record originated, distinguish a new event from a replay, preserve historical values, and reconcile data across systems. It also makes it easier to investigate why a metric changed or why two downstream tables disagree.
Database design is therefore not just a database administrator's concern. Backend developers, analytics engineers, data engineers, architects, and application developers all contribute to the quality of the data model.
The most common design mistake is beginning with tables before understanding the work the database must support. Start by identifying users, business processes, data sources, and the questions the system must answer.
Write down what the system is responsible for and what it is not responsible for. A customer analytics platform may combine sales, support, and web activity, but it may not be the system of record for payments or customer authentication. Clarifying system boundaries helps avoid duplicated ownership and confusing update rules.
Identify the authoritative source for each important fact. If an order's payment status belongs to a payment service, an analytics warehouse may store a copy for reporting, but the warehouse should not become an alternative authority that independently changes the payment state.
List the operations that will run most often. Include reads, inserts, updates, deletes, batch loads, reports, exports, and administrative tasks. For each operation, note expected frequency, concurrency, response-time expectations, and approximate data volume.
For example, an order application may need to:
These operations imply different access patterns. Retrieving one order by identifier is a selective lookup. Producing a monthly sales report is an aggregation across many records. A schema and index strategy should reflect both, while recognizing that operational and analytical workloads may belong in separate systems.
Estimate the number of records created per day, average record size, retention period, and expected growth. Include peak periods rather than relying only on averages. A system that records a few thousand events daily has different requirements from one that ingests millions of events per hour.
Also consider the shape of the data. Are records mostly small and structured? Do they contain large documents, images, or event payloads? Are updates frequent, or is the workload primarily append-only? These details influence storage, indexing, partitioning, and archival decisions.
Document requirements that are not visible in a simple entity diagram:
A short requirements document is often more valuable than an elaborate diagram built on untested assumptions.
An entity represents a distinct type of thing or concept about which the system stores information. In a relational model, entities commonly become tables. Attributes describe the properties of each entity and become columns.
Read process descriptions, forms, reports, API payloads, and source datasets. Look for important nouns, but do not automatically turn every noun into a table. A status such as pending may be a controlled attribute rather than a separate entity. A product category may deserve its own table if it has attributes, lifecycle rules, and relationships of its own.
For a basic commerce system, candidate entities might include:
Define each entity precisely. If two teams use the word customer to mean different things, the model should expose that difference rather than hide it behind one ambiguous table.
Each attribute should represent one well-defined fact. A customer may have a customer ID, display name, email address, account creation timestamp, and account status. Avoid storing unrelated facts in one text field merely because the initial application needs only to display them together.
Consider whether a value is required, optional, derived, time-dependent, or sensitive. A customer's current address is not necessarily the address used for an order placed last year. If historical accuracy matters, preserve the address associated with the transaction or model address history explicitly.
Relationships describe how entities connect. The common patterns are:
In a relational model, a one-to-many relationship is usually represented by a foreign key on the many side. A many-to-many relationship is usually represented by an associative or junction table. For orders and products, an order_items table connects the two and stores relationship-specific attributes such as quantity and unit price at the time of purchase.
An entity-relationship diagram (ERD) shows entities, attributes, keys, and relationships. It is a communication tool, not proof that the model is correct. Review the diagram with people who understand the source processes and the queries that applications must perform.
Check optionality as well as cardinality. A customer may have no orders, but an order must belong to a customer. A payment attempt may exist before it succeeds. These distinctions affect nullability, constraints, and application logic.
Keys allow a database to identify records and connect related information. A clear key strategy prevents duplicate entities and makes joins predictable.
A primary key uniquely identifies each row in a table. It should be stable, non-null, and independent of attributes that users may change. A generated integer, UUID, or domain-specific identifier may be appropriate depending on the system's requirements.
Do not use a person's email address as a primary key simply because it appears unique today. Email addresses can change, may be shared in some contexts, and can have normalization and case-sensitivity complications. Use a stable internal identifier and enforce email uniqueness separately when the business rule requires it.
A natural key has meaning in the business domain, such as a recognized product code. A surrogate key is generated primarily to identify a row, such as an integer identity or UUID.
Natural keys can be useful when the domain provides a stable identifier, but they may change or arrive with inconsistent formatting. Surrogate keys provide stable internal references, while a unique constraint can preserve the business rule for the natural key. Many systems use both.
A foreign key ensures that a value in one table refers to an existing row in another table. It prevents orphaned relationships and communicates the intended structure to both the database and future developers.
Choose delete and update behavior deliberately. Cascading deletion can be suitable for dependent records that have no independent meaning, but dangerous when records must be retained for audit or financial history. Restricting deletion may be safer when related records must be handled explicitly.
Constraints enforce rules close to the data:
Application validation is still important for clear user feedback, but it should not be the only line of defence. Multiple applications, scripts, and data pipelines may write to the same database. Database constraints protect the data regardless of which client submits a change.
Normalization organizes relational data to reduce unnecessary duplication and avoid anomalies. It helps ensure that each fact is stored in an appropriate place and that updates do not leave conflicting copies behind.
First Normal Form (1NF) requires each row-column intersection to contain a single value from the column's domain, rather than a repeating list of values. A customer table should not store several phone numbers as a comma-separated string if the application needs to search, validate, or update those numbers individually.
A separate customer_phones table can store one phone number per row, linked to the customer. This makes it easier to enforce uniqueness, mark a preferred number, and add or remove numbers without parsing text.
Second Normal Form (2NF) requires that every non-key attribute depend on the whole candidate key, not just part of it. It matters when a table uses a key made of multiple columns.
Suppose order_items uses (order_id, product_id) as a composite key. If the table also stores product_name, that value depends on product_id alone, not on the entire combination. Product details belong in the products table. The order item should store attributes specific to that product's presence in that order, such as quantity and the agreed unit price.
Third Normal Form (3NF) addresses transitive dependencies. Non-key attributes should depend on the key, the whole key, and not on another non-key attribute.
For example, if a customer table stores customer_id, postal_code, and region_name, and the region is determined by the postal code, the region value may be better represented in a related reference table. Whether that separation is worthwhile depends on the stability of the relationship and the system's requirements, but the dependency should be understood.
Poorly structured duplication creates three common anomalies:
Normalization reduces these risks, clarifies ownership, and makes transactional updates more predictable.
Denormalization intentionally duplicates or pre-computes information to improve a workload. It may reduce joins, accelerate common reads, simplify analytical queries, or support a data structure that is optimized for a specific access pattern.
Examples include storing a product name in an immutable order-line snapshot, maintaining a summary table for reporting, or embedding a small bounded set of related values in a document. These choices introduce maintenance responsibilities. The design must define which copy is authoritative, how changes propagate, and how discrepancies are detected.
Do not denormalize based on a vague assumption that joins are always slow. Measure representative queries first. A normalized schema with appropriate indexes can perform very well, and premature duplication often creates more problems than it solves.
No database model is ideal for every workload. Choose technology based on the shape of the data, query patterns, consistency requirements, operational capabilities, and the team's ability to run it reliably.
Relational databases organize data into tables with defined columns and relationships. SQL provides a powerful way to query and manipulate the data. Relational systems are a strong choice when relationships, constraints, transactions, and structured queries are important.
PostgreSQL, MySQL, SQL Server, and SQLite are common examples. Their capabilities and operational characteristics differ, so compare the features that matter to your workload rather than assuming they are interchangeable.
Relational databases are often appropriate for transactional applications, financial records, inventory, customer management, and systems where referential integrity is important. For practical PostgreSQL development, see Engineering Software with PostgreSQL.
Document databases store records as documents, commonly using JSON-like structures. They can be useful when records have varying attributes, when an application retrieves related information together, or when the data naturally maps to nested documents.
MongoDB is a widely used document database. Its flexible schema does not remove the need for design. Engineers still need to define validation rules, relationship strategies, document size boundaries, and access patterns. Embedding can make common reads efficient, while references can be more appropriate for shared or independently updated data.
A document model should not be chosen merely to avoid designing a schema. A flexible model still needs a clear understanding of how information is created, queried, updated, and retained.
Key-value databases associate a key with a value and are optimized for direct lookups. They are useful for caching, sessions, counters, and other access patterns where the key is known. Redis is a common example. It is usually a complement to a system of record rather than a replacement for every relational workload. See How Redis Accelerates Backend Performance.
Wide-column databases are designed for large-scale distributed workloads with access patterns that can be modelled around partition keys and query paths. Apache Cassandra is a well-known example. Graph databases represent entities as nodes and relationships as edges, making them useful for traversing complex connections such as networks, recommendations, and dependency graphs.
These systems require different modelling approaches. A design that works well in a relational database may not translate directly to a distributed wide-column or graph model.
Time-series databases optimize for timestamped measurements and event streams. Analytical databases and warehouses optimize for scans, aggregations, and large-scale reporting. Some platforms combine several capabilities, but the workload should still determine the model.
Polyglot persistence means using more than one data technology within a system. A platform might use PostgreSQL for transactions, Redis for caching, object storage for raw files, and a warehouse for analytics. This can be effective when each component has a clear purpose.
The trade-off is operational complexity. Data may be copied across systems, failures can occur between writes, and teams must define synchronization, ownership, monitoring, and recovery. Add a second database only when its benefits justify the cost of operating another system.
Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP) support different kinds of work. Treating them as the same workload often leads to poor performance and confusing data models.
OLTP systems handle the day-to-day operations of an application. They typically perform many short transactions that read or modify a small number of rows. Examples include creating an order, updating inventory, recording a payment, and changing a customer profile.
OLTP design generally emphasizes:
A transaction that creates an order and its line items should either complete consistently or roll back. The application should not leave an order header without its required items because one step failed.
OLAP systems support reporting, business intelligence, forecasting, and data science. They often scan large datasets, group records, calculate aggregates, and compare historical periods.
Analytical design generally emphasizes:
A query that calculates monthly revenue by product category across several years is fundamentally different from retrieving one order by ID. It may be better served by a warehouse or analytical database than by the operational application database.
A common architecture captures operational data in an OLTP database, moves changes through a batch or streaming pipeline, and loads them into an analytical platform. This reduces the impact of heavy reporting queries on customer-facing transactions.
The pipeline must still address data freshness, duplicate delivery, late-arriving events, schema changes, and reconciliation. The separation does not make those problems disappear; it gives the system a clearer place to manage them.
For related concepts, explore Explaining Data Warehouses, ETL vs ELT, and Real-Time Data Pipelines.
Dimensional modelling organizes analytical data around measurable business processes and the descriptive context needed to understand them. It is designed to make common analytical questions easier to express and execute.
A fact table stores measurements associated with a business event or process. In a retail warehouse, a sales fact may represent one order line and contain keys for date, product, customer, and store, alongside measures such as quantity, unit price, discount, and net sales.
Define the grain before choosing columns. Grain states exactly what one row represents. Is it one order, one order line, one payment attempt, or one daily product summary? A fact table with an unclear grain becomes difficult to aggregate correctly and can lead to double counting.
Measures may be additive, semi-additive, or non-additive. Sales amount can usually be summed across dimensions. An account balance may be additive across accounts but not across time. Ratios and percentages often need to be recalculated from their underlying components rather than summed.
Dimension tables describe the context around facts. A product dimension may include product name, category, brand, and product family. A date dimension may include calendar date, week, month, quarter, fiscal period, and holiday indicators.
Dimensions often contain descriptive attributes that make reports easier to read and filter. They are commonly denormalized to reduce the number of joins required by analytical queries.
A star schema places a fact table at the centre and connects it directly to dimension tables. It is simple to understand and often performs well for common analytical queries.
A snowflake schema normalizes some dimension structures into additional related tables. This can reduce repeated descriptive values but introduces more joins and complexity. Choose based on the warehouse engine, governance requirements, and the needs of analysts and reporting tools.
Dimension attributes can change over time. A customer's region, a product's category, or an employee's department may not remain constant. If historical reports should reflect the value that was true when a fact occurred, the warehouse must preserve history.
Common slowly changing dimension patterns include:
The right strategy depends on the business question. A current-state dashboard may use Type 1, while historical performance reporting often needs Type 2.
Some organizations use Data Vault modelling to integrate data from multiple sources while retaining source history and traceability. It separates business keys, relationships, and descriptive attributes into hubs, links, and satellites. It can support complex integration and audit requirements, but it also introduces additional structures and modelling overhead.
Do not select a warehouse methodology because it is fashionable. Choose a pattern that fits the number of source systems, change frequency, lineage requirements, query needs, and team experience.
A schema communicates meaning through its data types and constraints. Choosing them carefully improves correctness, storage efficiency, and interoperability.
Use integer types for whole-number quantities and appropriate decimal types for exact financial values. Floating-point types are useful for approximate scientific or statistical calculations, but they are usually unsuitable for representing currency where exact decimal arithmetic is required.
Use date and timestamp types rather than storing dates as arbitrary text. Decide whether timestamps represent UTC instants, local wall-clock times, or dates without time zones. Record time-zone context where the business meaning requires it. Confusing local time with UTC can create errors around daylight-saving transitions and cross-region reporting.
Choose text lengths and numeric precision based on real constraints, not guesses. Avoid overly narrow types that cause truncation and excessively broad types that hide invalid input.
NULL means the value is unknown, missing, or not applicable; it is not the same as an empty string or zero. Decide whether a field is genuinely optional. If an order must always have a customer, make the relationship non-nullable. If a delivery date is not known until shipment, it may be nullable.
Too many nullable columns can indicate that several distinct concepts have been placed in one table. Conversely, forcing fake default values into a column can make reports and application logic misleading.
Choose conventions for tables, columns, keys, indexes, and constraints. Consistency matters more than any universal naming style. Avoid ambiguous abbreviations and names that change meaning across teams. Prefer clear terms such as created_at, customer_id, and order_status.
Use plural or singular table names consistently. Document special conventions for timestamps, monetary amounts, units, and status fields. A column named weight is ambiguous unless the unit is known; weight_grams or a documented unit convention is clearer.
Do not assume every amount is in one currency or every measurement uses one unit. Store currency codes and define rounding rules. For measurements, store a unit or normalize values to a clearly documented base unit. This is particularly important when integrating datasets from multiple systems.
An index is an additional data structure that helps a database locate or order rows without scanning an entire table. Indexes can dramatically improve some queries, but they consume storage and add work to inserts, updates, and deletes.
Review the most important queries and identify columns used in filters, joins, sorting, and grouping. Index candidates often include primary keys, frequently searched foreign keys, and combinations of columns used together in common predicates.
For example, if an application frequently retrieves a customer's newest orders, a composite index on customer_id and created_at may be useful. The order of columns matters: a composite index is not automatically equally effective for every query that references any of its columns.
A B-tree index is a common general-purpose index for equality and range comparisons. Other index types support specialized workloads, such as full-text search, geospatial operations, or particular data structures. Availability and behavior vary by database engine.
A unique index can enforce a business rule as well as accelerate lookup. A partial or filtered index can index only rows that meet a condition in systems that support it. Covering indexes may allow a query to retrieve required values from the index without reading the full table row.
Every index has a cost. It consumes disk space, may increase memory pressure, and must be maintained when indexed data changes. Too many indexes can slow write-heavy workloads and make schema maintenance harder.
Use the database's query plan tools to inspect how important queries execute. Review estimated and actual row counts, scan types, join strategies, sort operations, and I/O. Test with representative data volumes; a query that is fast on a table with 500 rows may behave very differently with 50 million.
For deeper tuning, see SQL Query Optimization and Database Optimization Techniques.
Scaling decisions should follow measured constraints and a clear understanding of the workload. Partitioning and sharding solve different problems and introduce different operational trade-offs.
Partitioning divides a large logical table into smaller physical pieces according to a partition key, such as a date range or region. A query that filters on the partition key may be able to scan only relevant partitions, a process commonly called partition pruning.
Time-based partitioning can simplify retention and archival for event or transaction tables. However, partitioning does not automatically make every query faster. Poor partition keys, too many partitions, or queries that cannot prune partitions may increase complexity without delivering a benefit.
Sharding distributes data across multiple database nodes. A shard key determines where records are stored. A good shard key distributes workload evenly and supports common queries. A poor key can create hot spots, uneven storage, and expensive cross-shard operations.
Sharding adds complexity to transactions, joins, rebalancing, monitoring, and incident response. Prefer simpler approaches—query optimization, vertical scaling, caching, read replicas, or partitioning—when they meet the requirements.
Not all data needs to remain in the hottest storage tier indefinitely. Define how long operational records, logs, events, and derived data must be retained. Consider legal requirements, user expectations, recovery needs, and analytical value.
Archiving can reduce the size and cost of active datasets, but archived information must remain discoverable and recoverable when required. Document deletion and retention processes, including how they affect downstream copies and backups.
Replication can improve availability and support read scaling, but it is not a substitute for backups. Replicas may copy accidental deletions or corrupted writes. Maintain tested backups, point-in-time recovery where appropriate, and documented restoration procedures.
Define RTO and RPO with the people responsible for the service. Test recovery in realistic conditions rather than assuming that a successful backup job proves that the system can be restored.
A database in a data engineering system is rarely isolated. It receives data from applications, files, APIs, event streams, and other databases. Its design must support reliable ingestion and controlled change.
Pipelines should be designed to handle retries and duplicate delivery. A source event may be sent more than once because a consumer failed after processing it but before acknowledging it. Use stable event identifiers, idempotent writes, deduplication rules, and clear transaction boundaries.
For batch ingestion, record source file names, extraction windows, load timestamps, and processing status. For streaming ingestion, consider event time, processing time, ordering, late-arriving data, and replay behavior.
A layered architecture can preserve source information while creating progressively more useful datasets. A raw layer retains data close to its original form. A staging layer validates and standardizes it. Curated models apply business rules and prepare data for consumption.
The exact architecture varies, but avoid silently overwriting source evidence when traceability matters. Preserve lineage so that a downstream metric can be traced back to the source records and transformations that produced it.
Schema changes can affect applications, reports, pipelines, and integrations. Use version-controlled migrations rather than manually changing production tables without a record.
A migration should define what changes, how existing data is transformed, whether the operation locks or rewrites a large table, and how the application behaves during deployment. For a large table, a safer approach may be to add a nullable column, deploy code that can read both old and new forms, backfill in batches, add constraints after validation, and remove the old field in a later release.
Avoid assuming every migration is reversible. Some data transformations lose information. Prepare backups and a recovery plan, and test migrations against realistic data volumes. The Alembic Migrations guide explains schema versioning with SQLAlchemy.
Change Data Capture (CDC) records changes made to a source database so that downstream systems can process them. CDC can reduce the need for repeated full-table extracts and support near-real-time integration. Design for deletes, updates, transaction ordering, schema evolution, and replay. A downstream consumer must be able to recover after downtime without silently skipping changes.
Data governance is not a separate document that can be added after the database is complete. It affects what data is collected, who can access it, how long it is retained, and how its use is audited.
Identify public, internal, confidential, and sensitive data according to the organization's policy. Avoid collecting personal information that the application does not need. Minimize the number of systems and tables that contain sensitive values.
Separate sensitive attributes when doing so enables narrower access controls. For example, a broad analytics role may need a customer identifier and region but not a full address or authentication secret.
Give applications and users only the permissions required for their responsibilities. Avoid using one all-powerful database account for every service and analyst. Separate migration privileges from routine runtime access, and review permissions as teams and systems change.
Where supported, use row-level security, schemas, views, or controlled service interfaces to limit access. Remember that application-level authorization and database-level permissions serve complementary purposes.
Use encrypted connections and managed encryption capabilities appropriate to the platform. Store credentials and encryption keys in approved secret-management systems, not in source code or database tables accessible to ordinary application roles.
Sensitive values may require tokenization, masking, hashing, or encryption depending on their purpose. Passwords should be stored using a purpose-built password hashing algorithm, not reversible encryption or plain text.
Record where important data came from, which transformations were applied, and when datasets were updated. Audit logs can help answer who accessed or changed sensitive records, subject to privacy and retention requirements.
Define retention and deletion rules for the primary database, analytical copies, caches, exports, and backups. A deletion request may need to propagate through several systems. The design should make it possible to identify related records without relying on manual searches across disconnected stores.
Consider a data engineering platform that collects orders from an online store and prepares them for operational reporting and analytics. The platform needs to support customer-facing order operations while also answering questions about revenue, product performance, and delivery trends.
The operational application must create orders, store multiple line items, record payment attempts, and track fulfillment. It must prevent invalid references and preserve the price agreed at the time of purchase.
The analytics platform must calculate daily sales, revenue by category, average order value, payment success rates, and delivery time. It must retain historical product and customer attributes so that past reports remain meaningful after current records change.
A reasonable first logical model includes:
The many-to-many relationship between orders and products is resolved through order_items. That table stores quantity and a snapshot of the unit price, because the product's current price may change later.
The following PostgreSQL-style example illustrates the main relationships. It is a starting point, not a complete production schema; exact types, statuses, currency rules, and deletion policies should be adapted to the application.
CREATE TABLE customers (
customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
display_name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE products (
product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku TEXT NOT NULL UNIQUE,
product_name TEXT NOT NULL,
current_price NUMERIC(12, 2) NOT NULL CHECK (current_price >= 0),
is_active BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE TABLE orders (
order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
order_status TEXT NOT NULL,
currency_code CHAR(3) NOT NULL,
placed_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE order_items (
order_id BIGINT NOT NULL REFERENCES orders(order_id),
line_number INTEGER NOT NULL CHECK (line_number > 0),
product_id BIGINT NOT NULL REFERENCES products(product_id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(12, 2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, line_number)
);
CREATE TABLE payments (
payment_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(order_id),
payment_reference TEXT UNIQUE,
amount NUMERIC(12, 2) NOT NULL CHECK (amount >= 0),
currency_code CHAR(3) NOT NULL,
payment_status TEXT NOT NULL,
attempted_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
The example uses a composite key for order lines because a line number is unique within an order. A separate generated order_item_id could also be appropriate, particularly if other tables need to reference an individual line. The important point is to define the identity rule explicitly.
A production system should also define allowed status values, currency validation, payment idempotency, timestamp conventions, indexes, audit requirements, and lifecycle rules. Do not assume a text status column is safe simply because the application currently sends a limited set of values. Use a constraint, reference table, or another controlled representation that fits the database and deployment strategy.
Suppose the application frequently lists a customer's orders from newest to oldest. An index on customer_id and placed_at may support that access pattern. If staff frequently filter unpaid orders by status and date, a different index may be appropriate.
Do not add indexes merely because a column exists. Use representative queries and execution plans to decide. Confirm that the index improves the target workload enough to justify its storage and write overhead.
The warehouse can receive order and order-item changes through a batch or CDC pipeline. A sales fact table may use one row per order line. Dimensions can describe the product, customer, date, and sales channel.
A fact_sales table might contain:
The product dimension may use Type 2 history if reports must show the category that applied when a sale occurred. The fact table grain must remain one order line; aggregating to order level should happen in a separate query or model with clearly defined logic.
Test whether the model can answer the required questions without ambiguous joins or double counting:
If the answers require inconsistent definitions or manual workarounds, revisit the grain, keys, history strategy, or source ownership. A schema is not finished simply because all tables can be created successfully.
A table structure built only for one interface often becomes difficult to reuse. Understand the wider business process and likely integrations before fixing the model. This does not mean predicting every future feature; it means avoiding unnecessary coupling to one presentation.
Comma-separated IDs, phone numbers, or tags make filtering and validation difficult. Use related rows, arrays, or document structures only when their semantics and query requirements are clear. In relational systems, repeating groups often indicate that a separate table is needed.
A column named status, value, or type without a clear domain definition can mean different things to different teams. Use specific names and document allowed values. Avoid storing unrelated concepts in one field because they happen to share a data type.
Overwriting a customer's region or a product's category can make past reports inaccurate if the historical value matters. Decide which attributes require history and implement snapshots, effective dating, or slowly changing dimensions as appropriate.
A technology should solve a defined problem. A document database is not automatically more scalable than a relational database, and a distributed database is not automatically faster for every workload. Compare query patterns, consistency, operational burden, and team capability.
Indexes improve selected access patterns, not every query. Excessive indexes increase write costs and maintenance. Measure before and after changes, and remove indexes that no longer support a useful workload.
A backup that has never been restored is an untested assumption. Practice recovery, verify integrity, and document who can perform the process and how long it takes.
Untracked production changes create uncertainty about which schema version is deployed. Keep migrations in version control, review their effects, and document important business rules and ownership decisions.
A repeatable process helps teams make design decisions deliberately and review them before they become expensive to change.
A checklist cannot replace engineering judgment, but it makes hidden assumptions easier to discover. Revisit it whenever the system adds a new data source, changes a business process, or moves into a different scale of operation.
The following primary resources provide platform-specific guidance and deeper technical detail. Database features and supported syntax vary by engine and version, so consult the documentation for the exact platform you deploy.
Continue with Engineering Software with PostgreSQL, SQL Query Optimization, and Database Optimization Techniques for relational performance. For application architecture, see Backend Architecture, Software Architecture and System Design, and SQLAlchemy ORM. For data engineering, explore Explaining Data Warehouses, ETL vs ELT, Real-Time Data Pipelines, and Apache Kafka.
Database design is the process of structuring data, relationships, keys, constraints, and storage so that an application can manage information accurately and efficiently. It includes conceptual, logical, and physical decisions.
The main stages are requirements analysis, conceptual modelling, logical modelling, physical design, implementation, testing, and ongoing optimization. The process is iterative: real queries and operational experience may reveal changes that the original model did not anticipate.
A data model describes the concepts, entities, relationships, and rules used to organize information. A schema is the concrete structure implemented in a database, such as its tables, columns, keys, constraints, views, and indexes. The terms are related and are sometimes used interchangeably in everyday conversation.
Normalization is a set of principles for structuring relational data to reduce unnecessary duplication and prevent update, insertion, and deletion anomalies. The first three normal forms are common foundations for practical relational design, although higher normal forms may matter in specialized cases.
No. Normalization improves consistency and reduces redundancy, but queries may need joins across multiple tables. A denormalized structure can improve selected read-heavy workloads. The right choice depends on measured access patterns and the cost of keeping duplicated data consistent.
OLTP systems handle operational transactions such as creating orders and updating accounts. OLAP systems support analytical queries over larger datasets, such as calculating revenue trends and comparing performance over time. Their schemas and optimization priorities are often different.
Use relational SQL databases when structured relationships, transactions, constraints, and flexible querying are important. Consider a NoSQL model when its specific strengths—such as document-oriented access, key-value lookup, graph traversal, or distributed write patterns—fit the workload. Evaluate the actual data and operational requirements rather than treating the choice as a trend.
An entity-relationship diagram is a visual representation of entities, their attributes, keys, and relationships. It helps teams discuss the logical structure of a database and identify missing or ambiguous relationships before implementation.
A primary key is a column or combination of columns that uniquely identifies each row in a table. It must be unique and non-null. A good primary key is stable and should not depend on an attribute that users may change.
A foreign key is a column or set of columns that references a candidate key, usually the primary key, in another table. It helps enforce referential integrity by preventing invalid references.
Partitioning may help when tables become large, queries can filter on a useful partition key, or data lifecycle operations benefit from managing groups of records separately. It should be introduced after measuring a real need because it adds design and operational complexity.
A schema should change when requirements, data sources, or access patterns change—not according to an arbitrary schedule. Changes should be version-controlled, reviewed, tested, and deployed through a migration process that considers existing data and application compatibility.
Effective database design begins with understanding the information a system must manage and the work that information must support. A clear conceptual model, well-defined entities and relationships, stable keys, and appropriate constraints provide the foundation for reliable data. Normalization protects consistency, while deliberate denormalization and dimensional modelling can support specific analytical and read-heavy workloads.
For data engineering systems, the design must also account for ingestion, event replay, schema evolution, historical accuracy, governance, and the operational realities of running databases in production. Indexes, partitioning, and distributed storage should be selected in response to measured requirements rather than assumptions.
There is no universally perfect schema or database platform. The best design is the one that represents the domain accurately, supports the important workloads, protects data integrity, and can evolve without unnecessary disruption. Start with the requirements, test the model against real scenarios, document the trade-offs, and treat the database as a long-lived part of the system—not simply a place to put data.

Full-stack churn prediction platform using React, Django REST Framework, XGBoost, and SHAP. Enables telecom providers to identify at-risk customers, understand churn drivers, and support data-driven retention strategies.

Understand how data warehouses turn data from operational systems into trusted analytics. Explore modern architecture, ETL and ELT, dimensional modeling, governance, performance, and a practical implementation roadmap.
Read
Mastering SQL is the cornerstone of backend development excellence, empowering developers to build scalable, efficient, and data-driven applications that drive business success.
Read
Learn how data engineering pipelines collect, transform, govern, and deliver reliable data for analytics, AI, and machine learning. Explore architecture, tools, data quality, and practical workflows.
Read