I followed the suggestions of many experts in various discussions and I am changing the structure having fewer fields divided in more tables instead of fewer tables with many fields. In the meantime I am planning to reconstruct the relationship and related scripts.
Now I think I have a general question that might be useful for many people and being discussed many times but I want to be 100% sure of what I am doing before proceeding.
Basically the structure is:
Clients -----<Table_A (Clients::Primary_Key = Table_A::Foreign_key)
------<Table_B (Clients::Primary_Key = Table_B::Foreign_key)
Primary Key in Clients is calculated as Auto-Enter text field Get (UUID)
Clients Table has >40,000 records, Table_A,Table_B etc have some 20,000 to 80,000 records each
One aspect which is already clear to me:
when I am in Clients layout it is easy to create a new related record, for example, in Table_A
I use a script which copy Clients::Primary_Key into Table_A::Foreign_Key
The aspect that I don't have totally clear:
when I am in Table_A and I want to create a new record whose name is already stored in Clients, how do I get Table_A::Foreign_Key automatically entered ?
I have seen a few suggestions in various discussions, but a detailed explanation of what is the best practice would be very helpful.