Building A SQL table horizontally VS vertically

Question

What is a better design principle when designing SQL Server tables.

One can either add columns (ie grow horizontally) or grow it vertically (ie rather adding rows)

I have a situation when there are normally a number of items, say 2- 12 that are generally related to one another. Just to keep it simple, they would have the same values in 4 of their columns but 3 would be different.

Would it be better to now add the 12 entries in their own row, or would it be better to say add additional columns that would help me combine all the entries into one row, but be able to identify which one was which.

I dont think I have explained the situation well enough to get a specific answer relating to my situation.

But I am asking in general which is better practice?

Answer 1

The extreme example of the vertical design pattern is called entity-attribute-value. Generally speaking, you don't want to use the EAV design pattern with SQL Server due to challenges with indexing and constant joins.

To find the right middle ground, review Joe Celko's post on normalization in SQL Server Central's Stairways series. Note the sections on 3rd normal form.

Answer 2

The correct way doesn't exist... In dinamic table in SQL DB in my work I have seen equally horizontally and vertically. The road to follow is common sense applied to the specific context, generally the management software using horizontal tables because easier to manage dynamically at runtime, software for "mission-critical" or "special purpose" use vertical tables, the most elegant and efficient in performance but also more complex searches, and data reconstruction ...