a single, master table tracking all changes and the original values isn't really going to work well...think it through with me.if you want to keep old and new values, you also need to keep their datatype, right?

so your single, master tables going to need to either put everything in nVarchar columns, or have a suite of different columns,

one for each data type, so you can lnow that the column FirstName changed from 'Bob' to Robert, but also be able to track that the value Total Amount changed from 100 to 101.

that type of tables pretty difficult to work with, and gets a little unmanageable when you actually need to reverse a transaction out of it.

CDC puts the changes into a parallel table under the cdc schema, so changes to dbo.Invoices can be found in the mirrored table cdc.Invoices

it's very easy to query those tables in that case, and since they are tables, you could build a VIEW on those tables that could emulate the original structure you envisioned for the master table idea, but with all the advantages of CDC.

you mentioned robust and flexible, but what is the purpose....to recover/undo changes if needed, or to identify who made a change and when?