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:
Will be replicated using Transactional Replication.
Is CDC enabled.
Is fact based (not transactional based) i.e. like the concept of Products.
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.
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).