Dynamic Fields and Attributes with Relation (Like Zoho Creator)

Hi there,
Thank you for your great product.

So here i’m creating something like Zoho Creator.
It’s a SaaS Multi-Tenant service which every tenant has their own modules and each module has it’s custom fields. Users are completely Isolated.

I decided to use a structure like this in Database :

columns values
id 120000
namespace 1_2_2
module_id 1
created_at 2019-02-25 14:30:20
s01 Silver Price
s02 EUR
n01 88888
n02 999999
r01 10300
r02 10200, 10201, 10203

which fields begins with <s> is string, <n>s are numbers and <r> are relations.

So i have 2 questions.

  1. As you can see in my example, users can create a Module which can have has its own relations to another Module. Like “News Module” and “News Category Module”.
    So we will have news belonging to a category with <r> fields

    • There will be a relational filter and sorting. like: “Find me all news records posted from yesterday until now which have ( ( star_rating > 2 AND star_rating < 5 ) OR advertising_news = 1 ) AND category.parent_id IN 20 ORDER BY category.id”
    • Is this a correct way to do this or should i change something?
    • I’d Prefer not to save all relation data as a sub-document in news row, because every relation could have change very fast and i think it could be so hard to update all news records because of changing in title of a category.
  2. Is Sphinx/Manticore a good choice for this kind of project?
    There will be lots of users and lots of rows in the future. does performance decrease overtime?

Thank you for your time.

Joins between indexes are not supported in Manticore, so all relations will need to be brought in the index. If category title will be a full-text searchable field, you’ll need to perform mass replaces (if you use RT index) or delta reindexing (in case of plain indexes).
2. If your searches include full-text matches, then yes, that’s what they are made for. If you’re doing just data filtering (‘browsing’), it depends. There are cases when they can group/sort faster, but not always. What can be done in non-full-text queries is to have certain attributes. which normally would be filtered as attribute, as full-text keys - this works best when the values of those attributes have high cardinality (of course this doesn’t work for ranged values, like prices).

1 Like

Thanks Adrian