RDBMS
A Relational Database Management System (RDBMS) organises data into tables with rows and columns, enforcing relationships between tables through foreign keys. It's the most widely used database paradigm and the foundation of most business applications.
Core Concepts
- Tables: Collections of related data organised in rows and columns
- Rows (Records): Individual entries in a table
- Columns (Fields): Attributes of the data
- Primary Keys: Unique identifiers for each row
- Foreign Keys: References to primary keys in other tables
- Indexes: Data structures for faster query performance
ACID Properties
RDBMS guarantees:
- Atomicity: Transactions are all-or-nothing
- Consistency: Data remains valid after transactions
- Isolation: Concurrent transactions don't interfere
- Durability: Committed data survives system failures
Popular RDBMS Options
| Database | Strengths | Common Use Cases |
|---|---|---|
| PostgreSQL | Advanced features, extensibility | Our default choice |
| MySQL | Simplicity, wide hosting support | Web applications |
| SQL Server | Microsoft ecosystem integration | Enterprise Windows |
| Oracle | Enterprise features, support | Large enterprises |
| SQLite | Embedded, zero-config | Mobile apps, small projects |
When to Use RDBMS
Good fit:
- Structured data with clear relationships
- Transactions requiring ACID guarantees
- Complex queries with JOINs
- Reporting and analytics
- Most business applications
Consider alternatives for:
- Massive scale with simple access patterns → DynamoDB
- Unstructured or rapidly changing schemas → Schemaless
- Heavy analytical workloads → Column-store
What We Like
- Mature technology: Decades of optimisation and tooling
- SQL: Powerful, declarative query language
- Data integrity: Constraints and transactions protect data
- Flexibility: Handle diverse query patterns well
What We Don't Like
- Scaling complexity: Horizontal scaling requires careful planning
- Schema migrations: Changes can be painful in production
- ORM impedance mismatch: Object-relational mapping has limitations
Best Practices
- Design schemas thoughtfully: Normalization reduces redundancy
- Index strategically: Right indexes dramatically improve performance
- Use transactions: Group related operations appropriately
- Plan for migrations: Schema changes should be versioned and tested