What’s the better database design: more tables or more columns?

by

I have a few fairly simple rules of thumb I follow when designing databases, which I think can be used to help make decisions like this….

  1. Favor normalization. Denormalization is a form of optimization, with all the requisite tradeoffs, and as such it should be approached with a YAGNI attitude.
  2. Make sure that client code referencing the database is decoupled enough from the schema that reworking it doesn’t necessitate a major redesign of the client(s).
  3. Don’t be afraid to denormalize when it provides a clear benefit to performance or query complexity.
  4. Use views or downstream tables to implement denormalization rather than denormalizing the core of the schema, when data volume and usage scenarios allow for it.

The usual result of these rules is that the initial design will favor tables over columns, with a focus on eliminating redundancy. As the project progresses and denormalization points are identified, the overall structure will evolve toward a balance that compromises with limited redundancy and column proliferation in exchange for other valuable benefits.

More Related Contents:

  1. Is storing a delimited list in a database column really that bad?
  2. What are database normal forms and can you give examples? [closed]
  3. Normalization: What does “repeating groups” mean?
  4. In terms of databases, is “Normalize for correctness, denormalize for performance” a right mantra?
  5. have address columns in each table or an address table that is referenced by the other tables?
  6. Minimum no of tables that exists after decomposing relation R into 1NF?
  7. Should each and every table have a primary key?
  8. What’s the difference between identifying and non-identifying relationships?
  9. Database, Table and Column Naming Conventions? [closed]
  10. Can I have multiple primary keys in a single table?
  11. How are super- and subtype relationships in ER diagrams represented as tables?
  12. Difference between scaling horizontally and vertically for databases [closed]
  13. Relational table naming convention [closed]
  14. Best practices for storing postal addresses in a database (RDBMS)?
  15. When I should use one to one relationship?
  16. Why use a 1-to-1 relationship in database design?
  17. What is the optimal length for an email address in a database?
  18. Database optimization orders
  19. In what way does denormalization improve database performance?
  20. Best representation of an ordered list in a database?
  21. Alternatives to Entity-Attribute-Value (EAV)?
  22. Limit an sqlite Table’s Maximum Number of Rows
  23. NULL permitted in Primary Key – why and in which DBMS?
  24. What are some best practices and “rules of thumb” for creating database indexes?
  25. When we need to use 1-to-1 relationship in database design?
  26. Need help in developing DB logic
  27. What exactly does database normalization do?
  28. Databse architecture (single db vs client specific db) for Building Enterprise Web (RIA) application on cloud
  29. Derived account balance vs stored account balance for a simple bank account?
  30. uncertainty in developing a database model

Leave a Comment