Designing Database Tables and Relationships
A database is justified only when your application needs frequent random updates on large datasets, must handle data larger than available memory, or requires persistent storage across program restarts.
Choosing the Storage Tool
A database is not automatically the best way to store application data. Dictionaries and flat files are simpler to implement and are sufficient for small, static, or infrequently changing data. A database becomes justified when the application has a genuine need for capabilities that simpler storage methods do not provide as effectively.
Three Database Signals
The source identifies three primary scenarios that justify a database. These scenarios concern how often data changes, how much data exists, and whether the data must survive program restarts.
| Application need | Why simpler storage may become unsuitable | Database decision |
|---|---|---|
| Frequent random updates on large datasets | The application repeatedly changes individual pieces of a large collection | A database is justified |
| Data larger than available memory | The complete dataset cannot be held in memory at once | A database is justified |
| Persistent storage across program restarts | The application must retain data after it stops and starts again | A database is justified |
| Small, static, or infrequently changing data | A simpler representation may already satisfy the requirement | A dictionary or flat file may be sufficient |
Use requirements, rather than habit, to decide whether a database is appropriate.
The three database signals are not merely labels for database features. They are decision questions: Will the application frequently update individual records in a large dataset? Is the dataset larger than available memory? Must the information remain available across program restarts?
Tracing Data Size
Consider an application whose dataset grows beyond the memory available to the running program. An in-memory dictionary is no longer a complete solution for holding that dataset, because the entire collection cannot fit in memory. This is one of the situations in which a database becomes justified.
Evaluating a Growing Dataset
An application begins with a small collection of records, but the collection grows beyond the memory available to the running program. Which storage question should the developer ask?
Identify the limiting condition: The important condition is not simply that the collection is large in an abstract sense. It is that the data is larger than the memory available to the application.
Compare the storage choices: A dictionary is an in-memory storage method. If the complete dataset cannot fit in available memory, it does not meet the application's requirement by itself.
Apply the database criterion: A dataset larger than available memory is one of the three primary scenarios that justifies using a database.
The application has a database requirement because its dataset is larger than the available memory.
Tracing Persistence
A second important trace follows the application through a stop-and-start cycle. If the data must still exist after the program stops and starts again, the application requires persistent storage across program restarts. This requirement is another primary reason to use a database.
What do you think happens?
An application must preserve its data when the program stops and starts again. Does this requirement count as one of the primary reasons to use a database?
Reveal answer
Answer: Yes
Persistent storage across program restarts is one of the three primary scenarios identified for using a database.
Balancing Complexity
A database provides powerful data-management capabilities, but database code is significantly more complex than code that works with Python dictionaries or writes to flat files. That complexity is a real cost. The decision is therefore a trade-off: the application gains database capabilities, but the implementation becomes more involved.
A Small, Infrequently Changing Collection
An application stores a small collection of data that changes infrequently. Should the developer introduce a database immediately?
Check the data characteristics: The collection is small and changes infrequently. These are conditions for which dictionaries and flat files are described as sufficient.
Account for implementation cost: Database code is significantly more complex than code using a dictionary or flat file, so that complexity needs a genuine justification.
Choose proportionately: If none of the three primary database scenarios applies, adding a database may introduce complexity without meeting an identified requirement.
A simpler storage method is the appropriate starting point for this example.
Structuring Related Tables
Once an application genuinely needs a database, design becomes important. Most real-world databases require multiple tables with relationships between them rather than one single flat table. The source also emphasizes proper design and normalization. The structure chosen for tables and relationships directly affects program performance.
For this topic, normalization should be treated as a design concern rather than a decoration added after implementation. The source connects database design and normalization with program performance, so table structure should be planned carefully once the database decision has been made.
Do not stop at the decision to use a database. Invest in proper table design and normalization. Because the structure of tables and relationships affects program performance, database design is part of implementation rather than an afterthought.
Common Design Mistakes
Choosing a database automatically for every application
Dictionaries and flat files are simpler to implement and can be sufficient for small, static, or infrequently changing data.
Fix:
Check first whether frequent random updates, data larger than available memory, or persistence across program restarts is actually required.Ignoring the implementation cost
Database code is significantly more complex than code using simpler storage methods.
Fix:
Treat added complexity as a cost that must be justified by the application's requirements.Treating database design as a single-table problem
Most real-world databases require multiple tables with relationships between them.
Fix:
Plan related tables and invest in proper design and normalization.Delaying design and normalization until after implementation
The source states that table structure, relationships, and normalization directly affect program performance.
Fix:
Design carefully when committing to a database.
Decision Practice
For each situation, decide whether a dictionary or flat file may be sufficient, or whether one of the three primary database scenarios is present. Explain which requirement led to your decision.
Hints
- Look for frequent random updates on a large dataset.
- Check whether the data is larger than available memory.
- Check whether the data must persist across program restarts.
- If none of these applies and the data is small, static, or infrequently changing, consider a simpler method.
- A small collection that rarely changes and only needs to be used while the application is running.
- A large dataset in which the application frequently changes individual records.
- A dataset that must still be available after the application stops and starts again.
- A dataset larger than the memory available to the running program.
A good answer names the requirement, not just the storage tool. For example, the correct reasoning is that persistence across program restarts justifies a database, rather than simply saying that databases are better.
Practical Summary
- Use a database when the application needs frequent random updates on large datasets, data larger than available memory, or persistence across program restarts.
- Use dictionaries and flat files when the data is small, static, or infrequently changing and no database requirement is present.
- Remember that database code is significantly more complex, so the application's needs must justify that cost.
- Most real-world databases use multiple related tables rather than one flat table.
- Invest in proper design and normalization because table structure and relationships affect program performance.
Key Takeaways
- A database is justified by specific application requirements, not by the fact that it is a powerful technology.
- The three primary signals are frequent random updates on large datasets, data larger than available memory, and persistence across program restarts.
- Dictionaries and flat files remain appropriate for small, static, or infrequently changing data.
- Database capability comes with significantly greater implementation complexity.
- When a database is necessary, related tables, careful design, and normalization matter because they affect program performance.