your initial idea is ok.
you just need to populate the small table with the record instances of the big one (typically importing una tantum all the PK's of the big one into the FK of the small one) and modifying the New Record script (you do have one, don't you ?) of the big table such that it creates a record in the small table - best implemented with a global var relationship which allows related records to be created and setting a field in the small table via this rel, which will create a new record. More on this technique is, for example, here.
I didn't realise you could create a script for new records!
Where do I set it up? I only know about scripts triggered on layouts. If there's a new record script trigger that would solve my problem - thanks.
there's no such thing, but if new record creation happens exclusively via a new record script and an Import script, then you can undertake the right actions in order to have the small table be in sync with the big one.
Just disable the record creation for user accounts and constrain them to use a script running with full privileges.
I see what you mean, but I can't see myself being popular if I tell them they can't just use File -> Import to import new data as they usually do.
I presume the script would be pretty basic, though - just a call to ImportRecords with the full access checkbox ticked. They can then do everything else manually, and I can then loop through the found set creating the related records... That might well be the simplest way out of this.
Thanks for taking the time to think about it for me.