I have a simple database representing collections of entities and sets of those collections. Sets contain multiple collections and collections contain multiple entities. Currently, when I query all the entities belonging to a given set I'm returning a union of all the entities in each collection belonging to that set. How could I redesign the schema and queries to allow for more flexible set logic? For instance given 4 collections, when I select all the entities in set 1, I want to get (collection 1 INTERSECT collection 2 EXCEPT collection 3) and when I query set 2 I want to get (collection 3 UNION (collection 2 INTERSECT collection 4)). In other words, I need a way to represent dynamic nested set operators in a relational database.
I can think of a few potential ways to accomplish this, suffice to say they are all non-trivial. What's the best practice for situations like this? Can anyone point towards any papers or articles on how to accomplish this? Thanks in advance.