Searching across multiple tables
I have a database with 40+ main tables. Each record in these tables has a related "meta" record that matches on its key. I want to be able to search for a string and be given all records across all main tables that have that string in any field.
I understand that I would not be able to view all the fields of the matched records in a single layout, as each table has different fields. I would settle for being able to just see the keys of these matched records. Since all records have a "meta" record, I could just show the key from that meta record on a layout. However, there is another hitch: when searching through all the tables, how do I save the records that match the string? I could copy them all into some generic related table, or have a "found" field in the meta record, but wouldn't that cause problems if multiple users are searching at once?
Is there any way to accomplish this without introducing race conditions and without restructuring my whole database?