Table contains multiple names for same company

For any application where you are tracking money changing hands, you should always use a Double Entry Accounting system. Double entry accounting has been the standard for tracking money for over 500 years. There is no good reason to use any other method.

This entails a TRANSACTION table and a TRANSACTION_DETAIL table. The transaction table has one record per transaction that includes header information, like the date, the total amount, any categorization attributes or relationships, etc.

The transaction detail has two or more records per transaction. These records break down where the money comes from and goes to. In your case, you might have three records per transaction. One shows the money coming in from the payment service. The other two show the money going out to a commission account and to an income account.

Strongly recommend you don't do it. There clearly are either technical deficiencies in the current database/app or you have contractual deficiencies that prevent your company from getting the 3rd party to fix/enhance their app/DB. Building a helper DB quickly becomes unmaintainable and you end up with errors that can be hard to troubleshoot.

For example, consider your scenario above where the company changes its name. If you can't modify the primary database to create triggers on that table or modify the app/SP so that changes to company name in the primary DB always ensure propagation to the helper DB, you're almost guaranteed to get out of sync over time. Regardless of whatever procedures you put in place, humans fail. On top of that, you've also tightly coupled another database to the primary so if the helper is not online and operational, there needs to be some mechanism in place to "hold changes" and make sure they get applied later. You also need to know what operations should not be permitted on the primary because they depend on the helper.

Those are just some top level things to deal with, there are more.

If you still want/have to do it, design the table logically the way you would if it was in the same DB. Using your customer name scenario, you'd have the column you want to track changes/variations of so for your example it would be company_name. You'll also need the column that authoritatively identifies the customer. This might be companyID or some other identifier the app uses. If you are on SQL Server 2016, enable temporal table for this and you'll have system managed versioning. Else, you'll need to do your own versioning. You'll need a column to track versions. Date is probably most natural but not necessarily the best for performance if you have a very large table. I suspect it won't matter for your case (if the table becomes very large, this won't be your biggest problem). You'll also need to consider creating a composite primary key or some surrogate key. Again, assuming the table won't be huge, I'd go with composite PK but you know your system best so evaluate options and recommendations carefully - this is a contentious subject.

Once you have that, create your database + tables and make sure you have the right permissions setup and whatever you do to protect the primary database, do the same for this helper DB. Make sure your backup plans will get you the right level of logical consistency and you have a scripted way to reconcile when they get out of sync.

Populating the first time is easy, keeping it in sync is a bit trickier. Since you're not really just doing a 1-1 copy, things like replication won't work without some customization. Writing an ETL job to kick in regularly will do the trick and likely easier but what is the latency you can tolerate? Is it ok to have the helper DB 10 minutes behind the primary? 20? 30? Careful with the selection. Setting high frequency can cause interesting problems if a job gets blocked/delayed for whatever reason. Change tracking or change data capture are helpful here. They are not the same thing and picking the right technology really depends on your environment. At a very high level, if changes to the same row are very infrequent and if they occur, the same row would not be changed multiple times over a short period, change tracking might be a better solution - lighter weight. If the data may be changed repeatedly over a short period, consider change tracking - more overhead but you get full history to move to the helper.

A quick and dirty solution would be to write a trigger on the table so every time a value is changed, you would push that to the helper db but triggers are easy to mess up. It's easy to make a mistake and flood the database. Also, failures are often hard to troubleshoot. I wouldn't go this route.

Whatever you finally implement, be sure you test to ensure it works correctly for deleted companies. If you "blindly" propagate a delete to the helper DB, will that delete the latest or all occurrences? What is the right behavior for the business and are there legal requirements? How do you recover from an accidental delete?