LESSON 2: RELATIONAL DATABASE ARCHITECTURES

Lesson Overview

This lesson covers relational database architectures for Digital Product Passport implementations. Students will learn about product repositories, structured data storage, referential integrity, performance considerations, and how to design effective relational database schemas for DPP systems. The lesson provides practical guidance on leveraging relational databases for structured DPP data.

Learning Objectives

• Design effective relational database schemas for DPP data
• Implement product repositories with proper normalization
• Design referential integrity constraints
• Optimize relational database performance for DPP workloads
• Implement data access patterns for relational databases
• Manage database evolution and schema changes

Detailed Content

Relational Database Overview

Relational databases store data in tables with defined schemas, using SQL for data manipulation and query. For DPP systems, relational databases are ideal for structured data with well-defined relationships—product attributes, organization data, supply chain relationships, and evidence metadata. Relational databases provide strong consistency, referential integrity, and mature tooling.

Relational Model: The relational model organizes data into tables (relations) with rows (tuples) and columns (attributes). Tables are related through foreign keys that reference primary keys in other tables. This model enforces referential integrity and enables complex queries through joins. For DPP systems, the relational model naturally represents the structured relationships between products, organizations, and supply chain events.

ACID Properties: Relational databases provide ACID (Atomicity, Consistency, Isolation, Durability) guarantees. Atomicity ensures transactions are all-or-nothing. Consistency ensures database moves from one valid state to another. Isolation ensures concurrent transactions don't interfere. Durability ensures committed transactions persist even after failures. ACID properties are critical for DPP systems where data integrity is essential for regulatory compliance.

SQL Capabilities: SQL (Structured Query Language) provides powerful query capabilities. Capabilities include complex joins (query across multiple tables), aggregations (sum, count, average), subqueries (queries within queries), and window functions (analytic queries). These capabilities enable sophisticated analysis and reporting of DPP data. For DPP systems, SQL capabilities are valuable for regulatory reporting and supply chain analytics.

Maturity and Tooling: Relational databases have decades of maturity and extensive tooling. Tooling includes development tools (IDEs, schema designers), monitoring tools (performance monitoring, query analysis), and backup tools (automated backup, point-in-time recovery). Maturity and tooling reduce operational risk and accelerate development. For DPP systems, relational database maturity is valuable for long-term, mission-critical systems.

Product Repository Design

Product repositories store structured product data in relational databases. Effective design ensures data integrity, query performance, and support for complex product relationships.

Core Entities: DPP product repositories include several core entities. Entities include products (the primary product entity), product attributes (product characteristics), product classifications (categorization), product relationships (parent-child, component relationships), and product lifecycle (manufacturing, distribution, end-of-life events). Each entity should be represented as a table with appropriate columns and relationships. For DPP systems, core entities should align with CEDM product module.

Product Table: The product table is the central entity. Columns include product_id (primary key), product_name, product_type, manufacturer_id (foreign key to organizations), manufacturing_date, and other core attributes. The product table should have appropriate indexes on frequently queried columns (product_id, manufacturer_id, product_type). For DPP systems, the product table is the foundation of the repository.

Product Attributes: Product attributes can be stored in two patterns. EAV (Entity-Attribute-Value) pattern provides flexibility but complicates queries. Fixed columns pattern provides simplicity but requires schema changes for new attributes. For DPP systems, a hybrid approach is common—core attributes as fixed columns, extended attributes as EAV or JSONB for flexibility.

Product Relationships: Product relationships include parent-child relationships (assemblies and components) and reference relationships (related products). Relationships should be modeled through junction tables (many-to-many) or foreign keys (one-to-many, one-to-one). Junction tables for bill of materials include parent_product_id, child_product_id, quantity, and effectivity dates. For DPP systems, relationship modeling is critical for representing complex product structures.

Product Classifications: Product classifications categorize products according to taxonomies and standards. Classifications can be stored through junction tables linking products to classification codes. Tables include product_classifications with product_id, classification_system, classification_code, and effective dates. For DPP systems, classification tables enable regulatory reporting and filtering.

Referential Integrity

Referential integrity ensures relationships between tables remain consistent. For DPP systems, referential integrity is critical for maintaining data quality and preventing orphaned records.

Foreign Key Constraints: Foreign key constraints enforce referential integrity at the database level. Constraints ensure that foreign key values reference valid primary keys in related tables. For example, product.manufacturer_id must reference a valid organization.organization_id. Constraints should be defined for all relationships to prevent data inconsistencies. For DPP systems, foreign key constraints are essential for data integrity.

Cascade Actions: Foreign key constraints can specify cascade actions for updates and deletes. Actions include CASCADE (propagate changes), SET NULL (set foreign key to null), RESTRICT (prevent changes), and NO ACTION (similar to RESTRICT). Cascade actions should be chosen carefully—CASCADE can be convenient but may cause unintended data loss. For DPP systems, RESTRICT is typically preferred for critical relationships to prevent accidental deletion.

Constraint Enforcement: Constraints are enforced by the database during data modification. Enforcement occurs on INSERT (foreign key values must exist), UPDATE (foreign key values must remain valid), and DELETE (cannot delete referenced rows). Enforcement ensures data integrity but can impact performance. For DPP systems, constraint enforcement is necessary but should be monitored for performance impact.

Soft Deletes: Soft deletes provide an alternative to hard deletes with CASCADE. Soft deletes mark records as deleted with a deleted_at timestamp rather than physically removing them. This preserves referential integrity and enables recovery. Soft deletes should be indexed and filtered from queries. For DPP systems, soft deletes are valuable for maintaining audit trails and preventing accidental data loss.

Performance Considerations

Relational database performance is critical for DPP systems, especially for consumer-facing queries and high-volume data access. Performance optimization requires attention to schema design, indexing, and query patterns.

Indexing Strategy: Indexing is the primary performance optimization technique. Indexes should be created on frequently queried columns (product_id, manufacturer_id, product_type) and foreign keys. Composite indexes support multi-column queries. Indexes improve read performance but add overhead for writes. Indexing strategy should be based on query patterns and should be monitored for effectiveness. For DPP systems, indexing is essential for query performance.

Query Optimization: Query optimization ensures queries execute efficiently. Optimization includes using appropriate joins (INNER JOIN vs LEFT JOIN), avoiding subqueries where joins suffice, limiting result sets (LIMIT, pagination), and using covering indexes (indexes that include all queried columns). Query patterns should be reviewed and optimized based on execution plans. For DPP systems, query optimization is essential for consumer-facing performance.

Connection Pooling: Connection pooling reduces the overhead of establishing database connections. Pooling maintains a pool of reusable connections that applications can acquire and release. Pooling configuration should include pool size (based on concurrency), timeout settings (connection acquisition timeout), and validation (validate connections before use). For DPP systems, connection pooling is essential for high-concurrency scenarios.

Partitioning: Partitioning divides large tables into smaller, more manageable pieces. Partitioning can be by range (date ranges), list (specific values), or hash (hash of partition key). Partitioning improves query performance (queries can scan only relevant partitions) and maintenance operations (can operate on individual partitions). For DPP systems, partitioning is valuable for large product tables (partition by manufacturing date or product type).

Data Access Patterns

Data access patterns define how applications interact with relational databases. Effective patterns ensure efficient data access while maintaining data integrity.

CRUD Operations: CRUD (Create, Read, Update, Delete) operations are the foundation of data access. Create operations should use transactions to ensure related data is created atomically. Read operations should use appropriate queries and pagination. Update operations should use optimistic concurrency (version checking) to prevent lost updates. Delete operations should use soft deletes where appropriate. For DPP systems, CRUD operations should be implemented through data access layers that enforce business rules.

Transaction Management: Transactions ensure atomicity of multi-step operations. Transactions should be used for operations that modify multiple related tables (creating a product with attributes and relationships). Transaction boundaries should be as short as possible to reduce lock contention. Transactions should include error handling and rollback on failure. For DPP systems, transaction management is essential for maintaining data integrity.

Optimistic Concurrency: Optimistic concurrency prevents lost updates in concurrent scenarios. Implementation includes version columns (numeric or timestamp) that are checked on update. If the version has changed since read, the update is rejected. Optimistic concurrency is appropriate for web applications where concurrent updates are possible but not frequent. For DPP systems, optimistic concurrency is valuable for product data updates.

Batch Operations: Batch operations improve performance for bulk data operations. Batch inserts (insert multiple rows in single statement), batch updates (update multiple rows in single statement), and bulk copy (bulk data loading) reduce round-trips and improve throughput. Batch operations should be used for supplier data submission and bulk updates. For DPP systems, batch operations are essential for handling high-volume data loads.

Schema Evolution

Relational database schemas evolve over time as requirements change. Effective schema evolution ensures databases can adapt without disrupting applications.

Schema Versioning: Schema changes should be versioned to track evolution. Versioning includes schema versions (v1, v2, v3), migration scripts (SQL scripts to migrate between versions), and rollback scripts (scripts to revert changes). Versioning should be automated through migration tools. For DPP systems, schema versioning is essential for managing evolution over long lifecycles.

Migration Strategies: Different strategies handle schema changes. Strategies include expand and contract (add new columns, deprecate old columns), blue-green deployment (run both old and new schemas during transition), and feature flags (use flags to control new schema usage). Strategy selection should be based on change impact and downtime tolerance. For DPP systems, expand and contract with blue-green deployment is common for zero-downtime migrations.

Backward Compatibility: Schema changes should maintain backward compatibility where possible. Compatible changes include adding optional columns, adding new tables, and adding indexes. Incompatible changes require application updates and should be coordinated. Backward compatibility is particularly important for multi-party DPP systems where not all consumers update simultaneously. For DPP systems, backward compatibility is critical for ecosystem stability.

Data Migration: Schema changes may require data migration. Migration includes data transformation (transform data to new schema), data validation (verify migrated data), and rollback capability (revert if migration fails). Migration should be tested thoroughly in staging before production. For DPP systems, data migration should include validation to ensure data integrity is maintained.

Advanced Relational Features

Modern relational databases offer advanced features that can enhance DPP system capabilities.

JSON Support: Many relational databases now support JSON data types. JSON columns enable storage of semi-structured data alongside structured data. JSON support provides flexibility for evolving schemas while maintaining relational integrity for core data. For DPP systems, JSON columns are valuable for storing extended product attributes and evidence metadata.

Full-Text Search: Some relational databases include full-text search capabilities. Full-text search enables text search within text columns, enabling search without separate search engine. Capabilities include indexing text columns, phrase search, and relevance ranking. For DPP systems, full-text search can supplement dedicated search engines for simpler use cases.

Generated Columns: Generated columns compute values from other columns. Generated columns can be virtual (computed on read) or stored (computed and stored). Generated columns are useful for derived data (computed fields, concatenated values). For DPP systems, generated columns are valuable for maintaining computed attributes without application logic.

Materialized Views: Materialized views pre-compute and store query results. Materialized views improve performance for complex, frequently-executed queries. Views can be refreshed on schedule or on demand. For DPP systems, materialized views are valuable for reporting queries and dashboards.

Technical Concepts

• Relational Database: Database storing structured data in tables with SQL
• ACID: Atomicity, Consistency, Isolation, Durability properties
• Foreign Key: Column referencing primary key in another table
• Referential Integrity: Consistency of relationships between tables
• Primary Key: Unique identifier for table rows
• Index: Data structure improving query performance
• Transaction: Atomic unit of work
• Optimistic Concurrency: Version-based concurrency control
• Soft Delete: Marking records as deleted rather than removing them
• Partitioning: Dividing large tables into smaller pieces
• Connection Pooling: Reusing database connections
• Schema Evolution: Managing changes to database schema over time
• Materialized View: Pre-computed and stored query results

Architecture Considerations

Database Architecture: Design database architecture based on requirements. Consider single database (all data in one database) vs multiple databases (separate databases by domain). Single database simplifies joins and transactions. Multiple databases provide isolation and can scale independently. For DPP systems, single database with schema separation is common for simplicity, multiple databases for multi-tenant scenarios.

Normalization Level: Determine appropriate normalization level. Normalization reduces redundancy but can increase query complexity. Denormalization improves read performance but increases redundancy and update complexity. For DPP systems, Third Normal Form (3NF) is typically appropriate for core data, with selective denormalization for performance-critical queries.

Read Replicas: Consider read replicas for read-heavy workloads. Read replicas are copies of the primary database that serve read queries. Primary handles writes, replicas handle reads. Read replicas improve performance and scalability for read-heavy workloads. For DPP systems, read replicas are valuable for consumer-facing queries and reporting.

High Availability: Design high availability for critical databases. High availability includes primary-standby (standby takes over if primary fails), multi-AZ deployment (deploy across availability zones), and automatic failover. High availability should be designed based on RTO requirements. For DPP systems, high availability is essential for consumer-facing services.

Security Architecture: Design security for database access. Security includes network security (VPC, security groups), authentication (database credentials, IAM roles), authorization (least privilege access), and encryption (encryption at rest and in transit). Security should be defense-in-depth. For DPP systems, security is critical for protecting sensitive passport data.

Implementation Considerations

Database Selection: Select appropriate relational database. Options include PostgreSQL (open-source, feature-rich), MySQL (open-source, widely used), Oracle Database (commercial, enterprise features), and SQL Server (Microsoft ecosystem). Selection should be based on requirements, team expertise, and cloud provider preferences. For DPP systems, PostgreSQL is commonly used for its feature set and open-source nature.

ORM vs SQL: Decide between ORM (Object-Relational Mapping) and direct SQL. ORM provides abstraction and type safety but may generate inefficient queries. Direct SQL provides control but requires more boilerplate. Hybrid approach (ORM for simple queries, SQL for complex queries) is often optimal. For DPP systems, ORM for CRUD operations, SQL for complex queries is common.

Connection Pool Configuration: Configure connection pool appropriately. Configuration includes pool size (based on concurrency), timeout settings (connection acquisition timeout), idle timeout (close idle connections), and validation (validate connections before use). Configuration should be tuned based on workload characteristics. For DPP systems, connection pool configuration is essential for high-concurrency scenarios.

Migration Tooling: Use database migration tools for schema evolution. Tools include Flyway (Java), Alembic (Python), and EF Migrations (.NET). Migration tools provide versioning, automated migration, and rollback capability. For DPP systems, migration tools are essential for managing schema evolution across environments.

Monitoring Implementation: Implement comprehensive database monitoring. Monitoring includes query performance (slow query logs), connection metrics (pool utilization), resource utilization (CPU, memory, I/O), and replication lag (for read replicas). Monitoring should provide alerts for performance degradation. For DPP systems, database monitoring is essential for operational excellence.

Enterprise Examples

Battery Relational Database: A European automotive manufacturer implemented PostgreSQL for EV battery passport data. Schema included products table with core attributes, product_attributes EAV table for flexible attributes, and bill_of_materials junction table for component relationships. Foreign key constraints enforced referential integrity. Read replicas served consumer-facing queries. Partitioning by manufacturing_date improved query performance for recent data. The implementation supported 15+ year retention with strong data integrity.

Textile Relational Database: A European textile industry association implemented MySQL for textile passport platform. Schema included products, organizations, and classification tables with junction tables for many-to-many relationships. Soft deletes preserved audit trails. Materialized views pre-computed common reporting queries. Full-text search enabled product search without separate search engine. The implementation supported industry-wide data with multi-tenant isolation through organization_id foreign keys.

Electronics Relational Database: A consumer electronics manufacturer implemented Oracle Database for electronic product passport data. Schema included extensive referential integrity with cascade constraints for critical relationships. JSON columns stored extended product attributes for flexibility. Generated columns computed derived attributes (total weight from component weights). Advanced partitioning by product region enabled geographic distribution. The implementation supported global product portfolios with enterprise-grade reliability and performance.

Common Mistakes

Over-Normalization: Over-normalizing schema to the point where queries become complex and slow. Normalization should balance data integrity with query performance. Third Normal Form is typically sufficient; higher normalization often provides diminishing returns.

No Indexing: Not creating indexes on frequently queried columns, resulting in poor query performance. Indexes should be created on foreign keys, filter columns, and join columns. Indexing strategy should be based on query patterns.

N+1 Query Problem: Executing N+1 queries when fetching related data (one query for parent, N queries for children). This should be avoided through proper JOINs or eager loading. N+1 queries are a common performance issue in ORM-based applications.

Ignoring Transactions: Not using transactions for multi-step operations, resulting in partial updates and data inconsistency. Transactions should be used for any operation that modifies multiple related tables.

Large Transactions: Creating transactions that are too long or hold locks too long, resulting in lock contention and poor concurrency. Transactions should be as short as possible and should avoid user interaction within transactions.

Best Practices

Appropriate Normalization: Normalize to Third Normal Form (3NF) for core data, with selective denormalization for performance-critical queries. Balance data integrity with query performance. Denormalization should be justified and documented.

Comprehensive Indexing: Create indexes on foreign keys, frequently queried columns, and join columns. Use composite indexes for multi-column queries. Monitor index usage and remove unused indexes. Indexing is the primary performance optimization technique.

Transaction Management: Use transactions for multi-step operations. Keep transactions short and avoid user interaction within transactions. Include error handling and rollback on failure. Transactions ensure data integrity.

Connection Pooling: Use connection pooling to reduce connection overhead. Configure pool size based on concurrency, set appropriate timeouts, and validate connections. Connection pooling is essential for high-concurrency scenarios.

Schema Versioning: Use database migration tools for schema evolution. Version all schema changes, test migrations in staging, and maintain rollback capability. Schema versioning enables controlled evolution.

Performance Monitoring: Monitor database performance including query performance, connection metrics, and resource utilization. Use slow query logs to identify optimization opportunities. Monitoring enables proactive performance management.

Key Takeaways

• Relational databases are ideal for structured DPP data with well-defined relationships
• Product repositories include core entities: products, attributes, classifications, relationships, and lifecycle
• Referential integrity ensures consistency through foreign key constraints and cascade actions
• Performance optimization requires indexing, query optimization, connection pooling, and partitioning
• Data access patterns include CRUD operations, transactions, optimistic concurrency, and batch operations
• Schema evolution requires versioning, migration strategies, backward compatibility, and data migration
• Advanced features include JSON support, full-text search, generated columns, and materialized views
• Architecture considerations include database architecture, normalization level, read replicas, high availability, and security
• Implementation considerations include database selection, ORM vs SQL, connection pool configuration, migration tooling, and monitoring
• Common mistakes include over-normalization, no indexing, N+1 query problem, ignoring transactions, and large transactions
• Best practices include appropriate normalization, comprehensive indexing, transaction management, connection pooling, schema versioning, and performance monitoring