This week, I compared two different SQL database schemas designed to manage a prescription system. Both versions handled similar entities — doctors, patients, drugs, pharmacies, prescriptions, and refills — but they differed in structure, naming conventions, and design philosophy. Through this comparison, I gained a deeper understanding of how database design choices impact readability, normalization, and maintainability.
Database Structure and Naming Conventions
The first schema used descriptive, Java-friendly column names such as doctor_id, patient_id, and practice_since_year. This made it easier to map directly to Java entity classes in an application using Spring or Hibernate. The second schema simplified the design by using id for all primary keys and shorter names like name or zip, resulting in a more compact and uniform structure.
I learned that naming conventions play a big role in collaboration between a database and application code. A schema with consistent names across tables reduces confusion and simplifies foreign key relationships, especially in larger systems.
Normalization and Redundancy
The second version demonstrated stronger normalization principles. For example, instead of a verbose fill_request table that repeated foreign keys for the doctor, patient, and drug, it used a cleaner fill table linked directly to the prescription table. This avoids redundancy because all those details can already be retrieved from the prescription record.
This helped me appreciate the concept of avoiding data duplication — storing information once and referencing it when needed, which minimizes inconsistency and makes updates easier.
Inventory and Pricing Design
Another key difference was how each schema handled pricing. The first version had a pharmacy_inventory table to track drug quantities and costs. The second used a drug_cost table with a composite key (pharmacy_id, drug_id, quantity) to represent specific price combinations.
From this, I learned that there’s often more than one valid way to represent the same real-world relationship. The pharmacy_inventory design is easier to visualize, but the drug_cost version is more flexible and follows relational design principles better.
Data Integrity and Constraints
The second schema enforced stronger constraints by marking certain fields (like birthdate) as NOT NULL and by using a recreated database setup (DROP DATABASE IF EXISTS). These small details make the database more robust and reliable during development and testing.
I realized that data integrity constraints are essential to prevent incomplete or invalid records from being inserted, ensuring long-term consistency in a production environment.
Practical Takeaways
Through this exercise, I learned several valuable lessons:
- Well-chosen field names make code integration and debugging smoother.
- Normalization helps prevent redundancy and keeps databases easier to maintain.
- Composite keys can represent complex relationships cleanly.
- Enforcing constraints early reduces data issues later.
- Including starter data (as in the second schema) is useful for testing queries quickly.
Reflection
Overall, the comparison helped me understand the trade-offs between readability and normalization. The first schema felt more explicit and beginner-friendly, while the second was more concise and professionally structured. If I were designing a real-world prescription system, I would combine elements from both — keeping the clarity of descriptive field names while following the normalization and integrity practices shown in the second version.