Although Normalization and partitioning both produce a rearrangement of the columns between tables they have very different purposes.
Normalization is first considered during logical datamodel design. It is a set of rules which ensure that each entity type has a well-defined primary key and each non-key attribute depends solely and fully upon that primary key.
Partitioning comes in during physical database design, when we start to map logical attributes to physical columns and determine the operational characteristics required from the system. Sometimes it is an optimisation added after testing under load because performance was found to be inadequate. It can also play a role in implementing a data retention policy.
In partitioning we recognise that a table is made from rows and columns. When we partition we separate some of those rows (or columns) from the others and hold them in a physically different location.
Horizontal partitioning is when some rows are stored in one table, and some in another. There could be many sub-tables. A typical example is when currently-active transactional data is separated from old "archive" data. This keeps "hot" data compact, with associated performance improvements. We many be able to make the archive tables read-only, compressed and on cheaper disk, too.
As the next step each partition may be moved onto separate hardware. This is commonly know as "sharding." Advantages include being able to use many cheaper boxes rather than one very large, very expensive server, and being able to position a user's data geographically close to her. The cost is increased application complexity. Some DBMS incorporate this ability natively.
Vertical partitioning is when some columns are moved to a different table or tables. Similar to horizontal partitioning the motivation is to keep the "hot" table small so access is faster. Say you run an e-marketing company. 99% for the time you need a person's name and email address and nothing else. These will go in one table and all the other stuff which is useful but seldom-used - birthday, golf handicap, PA's phone number etc. - go in a different table. It can also help when the partitions have different update regimes or are owned by different sections of the business. The two tables can have the same primary key column, and corresponding rows could have the same key value. While it is possible to have multiple vertical partitions for a table, and to shard vertically, I've never come across it.
Vertical and horizontal partitioning can be mixed. One may choose to keep all closed orders in a single table and open ones in a separate table i.e. two horizontal partitions. For the open orders, order data may be in one vertical partition and fulfilment data in a separate partition.
The techniques I've talked about are ways to change the design to improve performance. Scaling is when you change the hardware. One can scale up by buying a bigger box with more RAM, CPU or faster disk, or scale out by moving some of the work onto a different box. Scale up is sometimes called scaling vertically whereas scale out can be called horizontal scaling. While horizontal scaling and sharding have an obvious relationship they are not synonymous. It would be possible to use replication technologies to copy an entire database to another location for use by the users there, thus achieving scale-out, without having to partition any tables.
Firstly,
Change data capture is available only on the Enterprise, Developer,
and Evaluation editions of SQL Server.
So that may decide for you if any of your customers will not have the enterprise editions, or you don't yet know you will be using the enterprise editions. (As the spec includes "multiple future applications" this may be an real issue for you)
Unlike triggers it is not real time, this is both an advantage and a disadvantage. Using triggers always slow down an update.
I worked on one system when we used triggers (generated by CodeSmith), as well as tracking all the changes to the records, we also linked the changes together to a “history” table that included the module of the application that made the change, and the UI item the user used to make the change.
However you may be best solving this at the application level, by say writing all update to a message queue that is then replayed to create a database at any given point of time, see Temporal Patterns on Martin Flowler blog for a good overview of options.
Best Answer
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 TABLEandALTER TABLE Foo ADD Col1by 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.