Database Indexing (search question)

Data Service
1

Scenario: Here is a typical membership community. Each member has a Profile. Each Profile contains numerous tags. Like: London, Musician, Tennis, Piano, Tesla, etc, etc. Here’s a simplified schema.

Tag File:
Memb ID, string, indexed
Tag, string, indexed

Is there a way to search this Tag File efficiently? That would (for example) return just Memb IDs that match on London & Musician? Without having to create a London List AND a Musicians List and then Loop through the shorter list?

Or… would it be more efficient… NOT to have a Tag File, but rather place all Tags in a single string field named Tag, in the Profile File? Then search using some kind of “full text search” capability?
…

PS: Here’s a great example.

This forum! Click on the Search Magnifying Glass icon and type, “crud api” and instantly you get only the topics with CRUD and API in the heading. Wonderful! That’s what I want to do.

2

You can do in the same way as you would do directly in any other relational db.
It might look a little bit tricky, but…

  1. Create separate table for tags (for all tags).

  2. The Profile table should contain relation 1:N to the tags table.

  3. Request to the Tag table can look like this:

    GET
    https://api.backendless.com/<app_id>/<api_key>/data/Tags

    where=Profile[tags].objectId = ‘95A961F5-AB63-DDB9-FF0D-E1D0A2D95B00’ and (name = ‘London’ or name = ‘Musician’)
    property=count(name) as count_of_names

objectId is the id of the Profile record.
The count_of_names should equal the number of tags in your request.
If the amount of tags is less than in the request, so you have only partial coincidence.