7 Common Database Design Mistakes and How to Avoid Them

Core Message

This video identifies seven common, critical mistakes made during database design. The central theme is that thoughtful, deliberate design choices—such as using surrogate keys, normalizing data, and enforcing integrity at the database level—are crucial for creating a high-quality, efficient, and maintainable system. Avoiding these pitfalls early is significantly easier than fixing them later.

Key Mistakes and Solutions

• 1. Using Business-Relevant Fields as Primary Keys: Don’t use values like Social Security Numbers or part numbers as primary keys. These can change or are controlled by external entities.
  Solution: Use a surrogate key—a new, auto-generated ID field (e.g., ‘id’) that has no business meaning but uniquely and permanently identifies each record.
• 2. Storing Redundant or Calculated Data: Avoid storing data that can be derived from other fields, such as storing a person’s age when you have their date of birth. This creates maintenance overhead and potential inconsistencies.
  Solution: Calculate these values on-the-fly in your application or SQL queries as needed.
• 3. Using Spaces in Table or Column Names: While possible by using quotes, this makes queries messy, harder to read, and error-prone, as you must remember to use quotes every time.
  Solution: Use underscores (e.g., customer_order) or camel case to separate words in object names.
• 4. Neglecting Referential Integrity: Failing to enforce data rules at the database level. Relying solely on the application for validation is risky, as data can be entered through other means.
  Solution: Use database constraints (Primary Key, Foreign Key, Unique, Not Null, Check) to guarantee data quality and consistency.
• 5. Storing Multiple Pieces of Information in One Field: Storing a full address in a single text field makes it difficult to validate, filter, or query individual components like the city or zip code.
  Solution: Break composite data into atomic parts in separate columns (e.g., street_address, city, state, postal_code).
• 6. Using Separate Columns for Optional, Related Data: Creating multiple columns for similar optional data (e.g., home_phone, work_phone, mobile_phone) leads to many empty fields and is inflexible.
  Solution: Create a separate, related table (e.g., a phone_numbers table) to store these values. This allows for a flexible number of entries per record.
• 7. Using Incorrect Data Types and Sizes: Storing dates as text or using excessively large character fields (e.g., VARCHAR(2000) for a first name) wastes space, hurts performance, and compromises data quality.
  Solution: Choose the most specific and appropriately sized data type for each column (e.g., use a DATETIME type for dates).

Conclusion

Proactive and informed database design is fundamental to building robust applications. By understanding and avoiding these seven common mistakes, developers can ensure their databases are performant, scalable, and maintain data integrity over time.

Mentoring Question

Reflecting on your own projects, which of these design mistakes have you either made or encountered? What was the long-term impact on the application’s maintenance or performance?

Source: https://youtube.com/watch?v=s6m8Aby2at8&si=YgR2eI5EszbfVh6U