...
Option 1:
Having a column in the table itself simplifies a lot the queries since they can be translated directly (e.g. now complicated JOIN or IN clauses).
We could treat it as an eventual consistent operation if we want to speed up the transactions but I would not start with such an implementation for now considering that the amount of expected data will be limited. If we have to support very big tables (>100K rows) we should consider using a dedicated separate search index.
Note that since the virtual SEARCH_CONTENT column is not part of the column model of a synapse table, users cannot select or update its value.
Option 2 and 3:
This might the most flexible and simple solution (e.g. we do not have to do special work in the backend), users could decide to apply some pre-processing to the data that is indexed (e.g. stemming).
This options allow the users to “tweak” the search results, e.g. adding synonyms, removing unwanted tokens etc
Since the column is part of the model it is returned in the results for a
select *.This would potentially work automatically even with views
Design Review 09/13/2021:
Option 2 and 3 excluded, they put too much burden on the end user and we need an automatic solution
Consider a boolean at the table level rather than in the column model, this makes it more automatic for the user, in the backend we select the columns to index ourselves (with the possibility to extend it in the future so the user can customize which columns to index).
Add a specific parameter to the search query model on top of supporting a new SQL function to make it easier for the clients.