Databases and SQL

For a developer, the database is where an application's truth lives. Code can be redeployed in minutes; data written under a careless schema stays wrong until someone migrates it, often while the system is running. Database work in this sense means deciding how records are shaped and related, writing the queries that read and change them, keeping those queries fast as volumes grow, and making sure that operations which belong together either all happen or none do.

Three shapes of store

Habits the schema remembers

• Model the entities and the rules first, and let the database enforce them with keys, constraints and types rather than trusting every caller.
• Normalise to remove duplicated facts, then denormalise only where a measured query needs it.
• Add indexes for the queries you actually run, knowing that each one makes writes slightly dearer.
• Wrap operations that must succeed together in a transaction.
• Pass user input as query parameters, never by pasting it into the SQL string.
• Keep schema changes as versioned migrations alongside the code.
• Store files in object storage and keep only their references in the database.

SFIA separates two strands of this work. Database design is about specifying and maintaining the mechanisms that store and retrieve data; data modelling and design is about capturing what the data means and communicating it. Developers do both, often without naming them. Microsoft's DP-900 frames the ground from the data side: it asks candidates to describe normalisation, recognise common SQL statements and database objects, contrast relational with non-relational stores, and distinguish transactional workloads from analytical ones. Application developers live mostly on the transactional side. freeCodeCamp's Relational Databases certificate is a practical, free way to put SQL to work in a real terminal.

The mistake that shows up in almost every first project is the query inside a loop: fetch a list, then fetch the details for each item one at a time. An object-relational mapper makes this easy to write and hard to see, and it works perfectly until the list holds ten thousand rows. Learn to read the query plan and to ask for what you need in one round trip. The neighbouring topics are back-end development and APIs, SQL for analysis on the analyst's side, and cloud storage and databases for the managed versions.