Horizontal schema vs Vertical schema

Question

I am but a modest SQL DBA. I would like to request your advice on the design of the central part of our new proposed database.

Brent Ozar had something to say about this: Building A SQL table horizontally VS vertically. Please can either Brent or someone with a good deal of experience give me their feedback or experience in this matter?

Some details about the central part of our database:

1. Will be replicated using Transactional Replication.

2. Is CDC enabled.

3. Is fact based (not transactional based) i.e. like the concept of Products.

4. We want to be able to add new items easily as and when the requirement arises. If we go with the horizontal approach, then there will always be the requirement to add either additional tables or columns to existing tables.

5. We could partition all of our Entity Attribute Value tables based on a certain criteria to reduce the workload on the accessing of data?

I have worked with a Metadata based system for only one part of the (ever changing) system. The advantage of this was such that the configuration of data and workflows did not require extra development every time we needed to configure certain details required. Also, I had ensured that the data was easily accessible through clever use of indexes and partitions.

I really appreciate and respect any advice you have to offer regarding this issue (and regarding any misconceptions I might have).

Answer 1

Your choice does not have to either/or - you can have both.

At a "physical level" is generally better to use horizontal designs for performance, maintenance and reliability reasons. But the greater rule is: "Don't mix data and metadata". There is nothing intrinsically wrong with building an application that knows how to do CREATE TABLE and ALTER TABLE Foo ADD Col1 by reading metadata table and then allow the app itself to modify the data model. That way, you get the best of both worlds. Think of adding tables and columns the same way you think of DML. Of course, if you do this, you need to make sure you reconfigure the replication when the schema is changed.

Note that the above design guidance doesn't rule out having tables containing the metadata describing what the other tables do (so the app can configure itself to work with it). When building a data warehouse, this is typically an excellent idea. The metadata can even be used to generate and compile new application code.