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.

  • 1
    How dynamic should those custom fields be? Can new fields be added at any time, or can that only be done during installation/deployment, or does it need changes to the code and require a new release? Aug 14 '18 at 11:40
  • 1
    Yes they can be done anytime. Although we can assume such operations are rare. No, It does not need changes to code. You can add a new custom field from Admin UI.
    – Tuhin Dey
    Aug 14 '18 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.