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

Question :

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

Answer :

A former coworker insisted that a database with more tables with fewer columns each is better than one with fewer tables with more columns each. For example rather than a customer table with name, address, city, state, zip, etc. columns, you would have a name table, an address table, a city table, etc.

He argued this design was more efficient and flexible. Perhaps it is more flexible, but I am not qualified to comment on its efficiency. Even if it is more efficient, I think those gains may be outweighed by the added complexity.

Read More  Javascript – Applying class to an HTML tag given an attribute/value

So, are there any significant benefits to more tables with fewer columns over fewer tables with more columns?

,

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  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.

Read More  What’s your “best practice” for the first Java EE Spring project?

That’s the answer What’s the better database design: more tables or more columns?, Hope this helps those looking for an answer. Then we suggest to do a search for the next question and find the answer only on our site.

Disclaimer :

The answers provided above are only to be used to guide the learning process. The questions above are open-ended questions, meaning that many answers are not fixed as above. I hope this article can be useful, Thank you