Matching a database to the organization's needs
One competency in this course asks you to recommend databases and database management systems for an organization. A good recommendation starts with the work the data supports, not with a favorite product.
| Workload | What it looks like | Typical design | Examples |
|---|---|---|---|
| Transaction processing (OLTP) | Many small reads and writes by many users at once: place an order, take a payment, update a ticket | Normalized relational tables with strict transactions | MySQL, PostgreSQL, Microsoft SQL Server, Oracle Database |
| Analytics (OLAP, data warehouse) | Fewer but much larger queries that summarize history, such as sales by region by month for five years | Denormalized tables, often stored by column | Amazon Redshift, Google BigQuery, Snowflake, Azure Synapse Analytics |
| Embedded | One application on one device keeps its own data | A single database file, no server | SQLite, used inside phones, browsers and desktop apps |
| NoSQL | Huge volume, quickly changing structure, or special shapes such as networks of connections | Key-value, document, wide-column or graph stores | Redis, MongoDB, Apache Cassandra, Neo4j |
Among relational systems, the SQL is largely the same. The products differ in cost, hosting options, tools and the skills an organization already has.
| DBMS | License and cost | Strengths | Common fit |
|---|---|---|---|
| MySQL | Community Edition is open source (GPL) and free; Enterprise Edition is a paid Oracle subscription | Very widely used for web applications, large community, offered as a managed service by all major cloud providers | Websites, online stores, software-as-a-service products |
| PostgreSQL | Open source with a permissive license; free | Advanced SQL features and extensions such as PostGIS for map data | Applications that need advanced SQL or geographic data |
| Microsoft SQL Server | Commercial; free Express and Developer editions | Fits closely with Windows, Microsoft identity, Power BI and .NET | Organizations built on Microsoft tools |
| Oracle Database | Commercial; a free edition with size limits | Very large, mission-critical workloads and advanced features | Large enterprises, banks, ERP systems |
| SQLite | Public domain; free; no server to run | Zero setup; the whole database is one file | Mobile apps, small tools, prototypes |
Where the database runs is a separate decision.
- On-premises: the organization buys and runs its own servers. Full control, but it also owns patching, backups, hardware and staffing.
- Self-managed in the cloud: the DBMS is installed on a rented virtual machine. The provider supplies the hardware; the organization still administers the database.
- Managed database service (database as a service): the provider handles patching, backups, replication and failover. Examples include Amazon RDS for MySQL, Amazon Aurora, Azure Database for MySQL and Google Cloud SQL. The organization pays by the hour or month and gives up some control over settings.
- 1Describe the data: its structure (fixed fields or varied), its volume today and in three years, and how fast it grows.
- 2Describe the workload: many small transactions, large analytical queries, or both.
- 3List the requirements that cannot be traded away: correctness for money, uptime, response time, compliance rules such as HIPAA or PCI DSS, and where data must be stored.
- 4List the constraints: budget, licensing, staff skills, existing tools and hosting policy.
- 5Choose the data model first (relational, warehouse, NoSQL), then the product, then the hosting.
- 6Give the reason for each choice in the organization's own terms and name the trade-off you accepted.
An example: a regional staffing company schedules workers, records hours and invoices clients. Its data is structured (workers, clients, shifts, invoices), every hour worked must be recorded exactly once, auditors will ask for history, and the office already uses Microsoft 365 and Power BI. A relational DBMS fits because the data is structured and transactional. SQL Server or Azure SQL Database fits the existing Microsoft tools; MySQL on a managed cloud service fits a smaller budget. A document database would add risk without a benefit, because the data does not vary in shape.
Recommendation questions rarely have a best database in general. Match the stated need: transactions and strict consistency point to relational; massive, varied or rapidly changing data points to NoSQL; years of history for reports points to a data warehouse; a single offline app points to SQLite.
Structure plus transactions: relational. Scale plus flexibility: NoSQL. History plus reports: warehouse. One device: embedded.
Word bank
All definitions →- Data warehouse
- A database designed for analysis and reporting across history.
- Managed database service
- A cloud offering in which the provider runs patching, backups and failover.
- OLAP
- Online analytical processing: large queries that summarize historical data.
- OLTP
- Online transaction processing: many small, concurrent reads and writes.
- Open source
- Software whose source code is published under a license that allows use and modification.
Knowledge check
An online store must record orders and payments for thousands of customers at once, without losing or double-charging any order. What fits best?
A marketing team wants to analyze five years of sales history by region and month. What fits best?
Which option leaves patching, backups and failover to the cloud provider?