Databases for Applications

Brief

At Halcyon Clinics, a booking service reports a confirmed appointment even though its room reservation failed. A support analyst exports every patient column to find the problem, using the same powerful database account as the application. The operations lead says a nightly backup and a fast query prove the system is safe. Neither claim addresses the broken booking or the unnecessary disclosure.

You will follow one booking from its data model to its recovery evidence. The goal is to distinguish a relationship from an index, a filtered result from an access boundary, and a completed backup from a tested restoration. These distinctions determine whether the clinic can preserve accurate records, restrict access and explain a failure to an auditor.

A database control needs an enforceable mechanism and observable evidence: a promise in application code is not a substitute for database constraints, grants or restore tests.

Model relationships before writing queries

A relational model stores rows in tables with defined columns and constraints. Consider patients(patient_id, name) and appointments(appointment_id, patient_id, status). Each table's primary key is unique and non-null; appointments.patient_id is a foreign key referencing patients.patient_id. A required foreign key also needs a non-null constraint: otherwise a null value can represent an unassigned relationship. A foreign key prevents a reference to a nonexistent patient; it does not automatically authorize the person reading that patient's records.

A document model groups fields and nested collections into documents. Embedding a booking's small, bounded list of preparation instructions makes one-document reads convenient. Embedding a patient's name into 200 independent booking documents duplicates it, so a name correction requires coordination or an explicitly accepted stale snapshot. Document databases can enforce validation and may support multi-document transactions; relational databases can also store nested data. Choose boundaries based on update consistency, relationships and access patterns, not a claim that one model has no schema.

For integrity assurance, preserve schema definitions, relationship constraints and rejected-write test results. An auditor can ask whether an appointment with patient_id 999 is rejected when that patient does not exist. A diagram alone documents intent; the enforced constraint and its test demonstrate operation.

Read a query and its access path

SELECT chooses result columns; WHERE filters rows; JOIN combines matching rows. In SELECT a.appointment_id, p.name FROM appointments a JOIN patients p ON a.patient_id = p.patient_id WHERE a.status = 'confirmed', the aliases identify each table and the ON expression matches its relationship. With patients 42 and 43 and confirmed appointments 701 and 702 both for patient 42, the result contains two rows, not one patient row. An inner join omits unmatched rows; a left join retains left-side rows with null right-side fields. Joining without the relationship predicate can multiply rows and corrupt evidence counts.

An index is an additional lookup structure, commonly an ordered tree, which can reduce rows examined. An index on (patient_id, status) often helps queries constrained by patient_id and then status; a status-only query may not benefit the same way. On a million-row table, a selective lookup might inspect tens of matches rather than scanning every row. The optimizer still chooses a plan based on statistics and estimated cost. Indexes consume space and add work to inserts, updates and deletes; an ordinary index does not enforce referential integrity or uniqueness.

Use bound query parameters for untrusted values rather than concatenating text into SQL. A WHERE clause requesting one clinic's rows is not a permission boundary if callers can alter the query. Review grants and any database-enforced row policies separately. For evidence queries retain the query, parameters, collection time and row-count reconciliation; inspect the execution plan before adding indexes solely because a report is slow.

Transactions protect a business change

Treat creating appointment 701 and reserving its room as one transaction: begin, perform both writes, then commit only if both succeed; otherwise roll back. Atomicity means all-or-nothing for that transaction. Consistency means declared invariants remain satisfied when valid transactions run; it does not mean the database knows every business rule. A unique constraint on a room and time slot can reject a duplicate reservation that application-only prechecks miss.

Isolation controls what concurrent transactions observe. Two requests can both read an available slot before either writes; a transaction alone does not guarantee that arbitrary read-then-write logic is race-free. Appropriate constraints, locking or stronger isolation are needed, with retry handling for conflicts. Durability means acknowledged committed changes survive failures under the database's configured guarantees. Confirm persistence and replication settings rather than assuming a successful response proves resilience to every disaster.

Together these properties form ACID: atomicity, consistency, isolation and durability. Integrity evidence includes transaction boundaries, constraint definitions and concurrent booking tests showing that only one contender succeeds. Ask which invariant was tested, which isolation behavior was configured and whether error handling can accidentally commit partial work. A fast index is no evidence that the two writes commit together.

Limit access and prove recovery

Give the booking runtime a dedicated account with only required operations on required tables; keep schema-changing migration credentials separate and time-limited. A reporting account may need SELECT on a restricted view, not write access or all patient fields. Review effective privileges, including inherited roles, and test denied operations. Protect connections with TLS and keep credentials out of source files. Least privilege limits impact even when a query or application is compromised.

A backup is a recoverable copy, not evidence that recovery works. A daily base backup plus continuous transaction-log archival can support point-in-time recovery: restore the base, then replay the unbroken log chain to a selected time before a destructive commit. If deletion commits at 14:07, test recovery to 14:06 in an isolated environment. An RPO of five minutes limits acceptable lost committed work; an RTO of 60 minutes limits restoration duration. Measure both in restore drills, verify record integrity, protect backup keys and retain copies outside the application's deletion authority. A replica can repeat an accidental deletion and is not a replacement for independent recovery copies.

Database audit logs should record relevant authentication, privilege changes and sensitive data access with timestamp, actor, action, object and outcome. Restrict and centralize them so the runtime account cannot erase its own trail; avoid recording secrets or unnecessary patient values. Transaction logs used for replay are not automatically a human-readable access audit. Auditors need effective grant exports, restore-test timelines and protected audit events tied to the review period, not just a green backup-job status.

Answers are graded on the server: the answer key never reaches your browser.