I have four tables
create table entities{
integer id;
string name;
}
create table users{
integer id;//fk to entities
string email;
}
create table groups{
integer id;//fk to entities
}
create table group_members{
integer group_id; //fk to group
integer entity_id;//fk to entity
}
I want to make a query that returns all groups where a user belongs, directly or indirectly. The obvious solution is to make a recursion at the application level. I’m wondering what changes can I make to my data model to decrease the database access and as a result have a better performance.
See Question&Answers more detail:os