Best way to setup Ecto schemas for a dynamic report builder?

[spyq]

I’m building a reporting tool that will allow users to define categories and tags for their products and then build custom sales reports through the UI for sorting and filtering against these tags/categories and sales data.

I understand that I can use Ecto “field” and “dynamics” to compose these queries, but I was wondering if anyone has advice for the ergonomic way to setup the table/schemas.

Specifically, I’m wondering if it would it be better to, say, add a discrete number of category fields to each product. Or, if it is reasonable to allow the user to define custom categories through something like JSONB. I’m just worried that the latter approach will add complexity to the query composition and isn’t worth the added flexibility.

Also, if anyone has suggestions for a library they’ve used that makes this easier please let me know!

Thanks for your advice!

[i-n-g-m-a-r]

You can create categories and tags tables.
Then you create product_category and product_tag tables, so you can join products and categories/tags.

You can also create user_category and user_tag tables, so you can filter categories/tags to be displayed.

Then when user selects a category/tag you can inner_join products and product_category and/or product_tag.

Inner joining is easily done dynamically.
Just add a join for every selected filter.
You could use Enum.reduce/3 to add the joins to a query.