I have a bunch of normalized tables, called danger, countermeasure and module.
Now I have a three column table krt representing the connection between the three tables. (column names danger_id, countermeasure_id, module_id)
The normal endpoints like /danger show the elements of the according table.
/krt?where={result: module, danger_id: x} would query the table krt for all dangers with danger_id == x and join the result with the module table.
The result would look like (converted for displaying)
danger_id:
- module a
- module b
danger_id2:
- module ..
[...]
I could of course provide a view and add a custom endpoint for this view. But there are not only three possible views but even more complex ones with one or two additional joins. (can also provide an example if needed)
Therefore is this kind of querying and joining a common concept or do I violate any of the REST constraints with this design? Is there any better / more intuitive way to provide such kind of information?
If you're asking about how would the RESTful URL would look, it might be something like
/krt?dangerId=x&result=module
How you decide to build that SQL query is not related to RESTful design in my opinion.
There's also no guideline saying that every GET request has to be cacheable - it depends on other factors, too. If your data is fairly static, but requested often enough, then you might get away with caching it for even a short period of time.