I have some user defined custom fields corresponding to a ticket (A ticket is an entity which has subject, status and custom fields). I need to search tickets based on its custom field values. A field can be a Decimal, String, Date or Boolean.
Example - A ticket has subject, status and custom fields - product name, product description, product id, product price, product category.
Use case - We need searching on custom fields. The cases are mentioned below.
- Case 1 - Normal equals searching on values. (searching based on product
- Case 2 - Range Searching on Decimal, Date types. (searching product whose price > 2000)
- Case 3 - Contains in string searching (searching product whose description contains food)
- Case 4 - A field can also be a multi select list (e.g. multi selection listbox) (A product can fall on multiple categories)
- Case 5 - We can delete or add a new custom field in system.
Possible solution - Separate table for custom field which has separate column for every custom field.
Cons : Since it is not pre defined which fields are eligible for searching, i have to add index on every column which will slow down insertion. Contains searching (ilike searching) is very slow.
I am currently using postgres but i am open to solutions involving nosql databases and elastic serach.