Dynamic Fields and Attributes with Relation (Like Zoho Creator)


#1

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.


#2

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).


#3

Thanks Adrian