Performance question: searching nested list field & database construction

Hi fellow bubblers:

I have a dilemma. I will read all @josh related posts but in the meantime I present you a common but tricky use case:

Let’s say we have a list of freelancers which each of them have a portfolio which in turn is a list of work. Each work inside each freelance portfolio has some details as for example hourly rate agreed, time spent, work category etc.

Now each work has also assigned a freelance as one of its fields.

Now let’s say I want to search freelancer that has an hourly rate of 25 usd/hour. And lets say that we have an unlimited number of freelancers and an unlimited number of works but the number of works is 20 times the number of freelancers.

From the performance and scalability perspective which option is the best?

  1. Do a search of freelance:filtered:advanced search of works on which the hourly rate is 25 usd/hour and the freelance field = this freelance?
  2. Do a search of work on which hourly rate is 25 usd/hour each item freelance?
  3. Copy each hourly work to the a new field on the freelance object which would be a list of hourly rates and then Do a search of freelances on which it’s list of hourly rates contains 25usd/hour

The last option would need some extra steps to save a new hourly rate every time a new work is saved in case a new hourly rate is used by the freelance.

I kindly ask for your support as this clarification will drive the whole construction of my app.

This topic was automatically closed after 70 days. New replies are no longer allowed.