I have two tables with a many to many relationship (invoices and their respective bank payments)
So I use a join table with the two foreign index fields.
Now then, I want to create many copies of these respective tables, such as Ledger01 Ledger02 and so on.
In my original spreadsheet layout the index field was/is called OurRef and so is the index field in the Bank01 and Bank02 etc also called OurRef. This approach facilitated pivot table reports with a pivot table helper column specifying the Source e.g Ledger01 or Bank01 etc.
I find that I am prevented from using the same name of (foreign) index field twice in the Joining Table (not a surprise, but a path to travel).
So even if my OurRef fields are "surrogate" keys, I can't use the spreadsheet approach, because there can be only one (Highlander?).
I am thinking this means I create a special surrogate key in each table and use that as the foreign key in the Joining table, but present the "old" key to the user if any such presentation is required?