I have a website-sideproject (Application Server: Django, Database: sqlite) with "search" functionality and I've recently stumbled over this problem.
When you have a full-text-search-functionality, typically you have some kind of data-duplication going on. Typically this is by storing your original data in some kind of NoSQL database (or similar, sqlite's fts5 functionality in my case) in a specific format for faster/better searching.
This creates the fundamental problem that you need to keep the data in your search database up to date with the data in your "real" database. Since, if you're doing web development, you're using the MVC pattern, there's 3 places where you could reasonably do this:
Do it in your original-database directly. This would be via triggers, one for each update/create/delete operation on each table in your original-database you want to search. Looks like the best option for ensuring data-integrity.
Do it in your model. The "How" for that is dependent on the framework, in my case with Django I'd likely have to write some custom "Manager" classes and tell my models to use those. Looks like the best option for maintainability.
Do it in your controller aka your application server. This would by through some kind of mixin, inheritance or similar that grants your controllers for creating/updating/deleting entries on your original-database the side-effect of also creating/updating/deleting an entry on your search-database. Looks also like a decent option but prone to causing bugs.
I'm not sure which is the best fit for what I intend to do. Here's the pros and cons that I've got so far:
Option 1) expresses my intent the best by firmly linking my "original" database and my "search" database, making sure the two are always in sync regardless of who connects through whatever means they want and changes anything.
Option 1) Might be the fastest option at runtime since you're executing things directly on the database. Given that you typically aren't receiving all that many update/delete/create requests though, how much that matters is questionable.
Option 1) is very hard to troubleshoot and do complex things in as SQL really isn't meant for combining data in any complicated sort of thing.
Option 2) expresses my intent well, but only as long as no other applications access the database (which is the case for me, but in general might be a point of consideration)
Options 2) + 3) are the easiest to code, as whatever backend language you have will be better suited if you have some more complicated logic that needs to be applied to get your data in the desired format for your search-database.
Option 3) runs the risk of bugs down the line, as you need to keep in mind to keep including the functionality that also updates the search-database down the line (be it through inheritance, mixin, decorator or whatever else).
Is the correct choice to put the logic in the model (Option 2.) ? Are there points to consider that I am missing?