Database Normalization and Best Practices
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.
The Storage Decision
Choosing a database is not automatically an upgrade. A database provides powerful ways to manage data, but the code needed to use one is significantly more complex than code based on dictionaries or flat files. The practical question is not whether databases are powerful. It is whether your application genuinely needs those capabilities.
Three Database Triggers
A database becomes justified in three primary situations. First, the application performs frequent random updates on a large dataset. Second, the dataset is larger than the memory available to the application. Third, the application must preserve data across program restarts. These are requirements for data management, not merely preferences about technology.
| Application requirement | Why it points toward a database |
|---|---|
| Frequent random updates on large datasets | The application needs to manage changing records within a large collection. |
| Data larger than available memory | The complete dataset cannot be held in the application's available memory. |
| Persistence across program restarts | The application must retain its data when one program run ends and another begins. |
The three primary situations in which a database is justified
Evaluating a growing records application
An application stores a large collection of records. Users frequently change individual records, and the records must still be available after the application is restarted. Which storage direction is justified?
- 1Check update behavior
Frequent changes to individual records in a large dataset match the first database scenario.
- 2Check lifetime
The records must survive program restarts, matching the persistence scenario.
- 3Choose the storage direction
Because the application meets more than one primary database requirement, a database is justified despite its greater implementation complexity.
Use a database rather than choosing a dictionary or flat file solely for simplicity.
Data Beyond Memory
A dataset that exceeds available memory creates a boundary between persistent storage and the application's working memory. The application cannot treat the complete dataset as though it were a small in-memory collection. A database is justified in this situation because it provides a way to manage data whose total size is beyond what the application can hold in memory.
The important distinction is total dataset size versus available application memory. When the dataset is larger than memory, holding everything in a dictionary is not an adequate design assumption.
Updates and Persistence
Two database triggers concern how data changes and how long it must live. Frequent random updates on large datasets require more than a convenient place to collect values: they require a tool intended for ongoing data management. Persistence adds a time dimension. If the application must retain data across restarts, storage must outlive one program run.
Complexity and Capability
Dictionaries and flat files are simpler to implement and are sufficient for small, static, or infrequently changing data. Database code is significantly more complex. That complexity is justified only when the application needs the database's capabilities, such as handling large changing datasets, data beyond memory, or persistence across restarts.
| Choice | Implementation burden | Appropriate situation |
|---|---|---|
| Dictionary | Lower | Small, simple, static, or infrequently changing data |
| Flat file | Lower | Small, simple, static, or infrequently changing data |
| Database | Significantly higher | Frequent random updates on large datasets, data beyond memory, or persistence across restarts |
Choose based on requirements, not on the assumption that a database is always better.
Design and Normalization
Once an application truly needs a database, implementation should not end with choosing the technology. Database design matters. Most real-world databases require multiple tables with relationships between them rather than one flat table. Normalization belongs to this design work: the way tables and relationships are structured can directly affect program performance.
For this topic, treat normalization as a design responsibility rather than a decorative database feature. The source material emphasizes two connected ideas: real-world databases commonly use multiple related tables, and the structure of those tables and relationships affects performance. Therefore, database design should be considered before the application grows dependent on an improvised structure.
Designing before scaling
A small application begins with simple data storage. Its dataset later grows, changes frequently, and must persist across restarts. What should happen when the application moves to a database?
- 1Confirm the need
The application now matches the database scenarios involving large changing data and persistence.
- 2Expect more than one table
Real-world databases commonly require multiple tables with relationships rather than one flat table.
- 3Normalize and review the design
The table and relationship structure should be designed carefully because it directly affects program performance.
Migrate when the requirements justify it, then treat database design and normalization as part of the performance work.
Common Decision Mistakes
Common mistake Choosing a database simply because it is more powerful
What it looks likeUsing a database for a small, static dataset that could be handled by a dictionary or flat file
Why it failsDatabase code is significantly more complex, and the added complexity is not justified when simpler storage is sufficient.
Do this insteadBegin with the simplest tool that satisfies the application's actual requirements.
Common mistake Ignoring dataset size
What it looks likeAssuming that an entire dataset can always be held in application memory
Why it failsA database becomes justified when the dataset is larger than available memory.
Do this insteadEvaluate the dataset in relation to the memory available to the application.
Common mistake Treating persistence as an afterthought
What it looks likeSelecting storage without asking whether data must survive a program restart
Why it failsPersistence across program restarts is one of the three primary scenarios that justify a database.
Do this insteadMake the required lifetime of the data part of the storage decision.
Common mistake Treating database design as a single-flat-table problem
What it looks likePlanning a real-world database without considering relationships between tables
Why it failsMost real-world databases require multiple tables with relationships.
Do this insteadInvest in proper design and normalization when adopting a database.
Storage Choice Practice
For each situation, choose a dictionary, a flat file, or a database. Then identify the requirement that led to your choice: dataset size, update pattern, persistence across restarts, or simplicity of implementation. Situation A: a small collection of mostly static configuration values. Situation B: a large dataset that is frequently changed one record at a time. Situation C: information that must still be available after the application restarts.
Hints
- Small, static, or infrequently changing data is sufficient for dictionaries and flat files.
- Frequent random updates on large datasets point toward a database.
- Persistence across program restarts points toward a database.
What do you think happens?
An application has a small dataset that rarely changes and does not need to retain data across program restarts. Is a database automatically justified?
Reveal answer
No, simpler storage may be sufficient
Dictionaries and flat files are simpler to implement and sufficient for small, static, or infrequently changing data. A database should be introduced when its capabilities meet a genuine requirement.
Practical Checklist
- 1Describe the dataset and decide whether it is small or larger than available memory.
- 2Determine whether the application performs frequent random updates on a large dataset.
- 3Determine whether the data must persist across program restarts.
- 4If none of these requirements applies, consider a dictionary or flat file first.
- 5If a database is justified, plan for greater implementation complexity, multiple related tables, and careful normalization.
- 6Review the design because table and relationship structure can affect program performance.
- 1A database is justified by requirements, not by prestige. The three primary triggers 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 because they are simpler to implement. When a database is necessary, its added complexity should be matched by careful design and normalization, since table and relationship structure affects program performance.
Key Takeaways
- Use a database when frequent random updates affect large datasets, when data exceeds available memory, or when data must persist across program restarts.
- Dictionaries and flat files are simpler and sufficient for small, static, or infrequently changing data.
- Database code introduces significant complexity, so the application's requirements must justify it.
- Most real-world databases use multiple related tables rather than one flat table.
- Careful database design and normalization matter because table and relationship structure can affect program performance.