I am developing a SaaS solution for CRM systems. All clients have one database. At the launch stage, I would not like to use separate search engines for FTS, while I use the PgSQL features.
Task 1
You need to be able to search for a customer by Last Name, First Name, Telephone Number, Email.
The phone number consists of two fields: phone_country_id and phone_number (country code and phone). I will solve this problem by collecting pre-data to the heap and when creating a record in the database I will build a search index for it.
For example:
'Pupkin Vasya': A, '+12223334455': B, 'vasya@pupkin.tt': C
Is my decision right?
Is FTS suitable for my task?
Are there dictionaries for Russian and English for searching by first and last names? Or is it nonsense? :)
Task 2
You need to be able to search for orders for any field from item 1 and for a number of fields from the order table (order number, creation date, source, etc.).
In the order with the user connects only one field - consumer_id.
As a solution, I chose the approach of 2 requests:
- Find all customers by pre-prepared index in the customer table.
- Select all orders of the found customers and filter them by the remaining fields.
If the search is on the order number, all other attributes are not taken into account.
Do I move in the right direction?
Was there something missing in the logic that I built?
