Forum Academy Marketplace Showcase Pricing Features

Dedicated table for searches & data synchronization

I am looking for a sanity check on my database design for searching: I want an app-wide search for items that exist in multiple tables: Users, Posts, and Assignments.

I am thinking of extracting the searchable data (names, post titles, post text, assignment name) and putting that data into a single separate table for easy searching.

The “search” table will have 3 fields:
Content (the stuff I want to search)
Type (what other table it’s in)
ID (unique ID of the thing the content came from)

The main tables will have a search_id field created for each field that’s getting sync’d to the search table, for example if I’m bringing over 2 fields from Users (first name and last name), I will create a firstname_searchid and lastname_searchid field in the Users table to tie the records together for updating purposes. The workflow to add/update the User field would also add/update the Search Content.

This way, the app-wide search can just search the Content field of the Search table. Am I thinking about this the right way or am I missing something that would make this a bad solution?