Relational Database is a type of a database that stores data in tables and every table has columns with data that accept certain values with some restrictions. Most if not all databases use a language called SQL to create tables and query data in a database.
How Relational Database works
Relational Database, during a query, opens a connection during which it performs a query. After that one query, database closes the connection and is inactive. However, if you want to perform a query that adds or updates more than one record in a database, you can use Transactions that allow you to do it, and fallback in case of failure.
Relationships
Relational Database, as the name suggest, allows you to create relationships between data. There are three main types:
When creating relationships, you can define Referencial actions which tell database what should happen with related data when related records are being deleted or updated.
Common practice
- Always select what should be returned from a Database table
- Database models should be created for a particular use case
Data migrations
Database Indexing
Relational Database Optimisations
Most of the time, you might have to add some replication for your database to optimise for either reads and writes, ensure it performs well and is highly available. Below are a few methods that you can choose from:
Replication
Disadvantages
- You are risking a data loss when your writes are failing while your master fails before any replica can take over
- Writes are replayed and when there’s loads of writes, your new replica might be bogged down and crash as well, which might create a domino effect
- The more slaves you have, the longer it takes to update them and you end up with replication lag
- Some systems, can write using multiple threads while read replicas can support writing sequentially
- Adds more hardware and therefore cost and complexity