What's the best practice for representing set operations in a relational database? 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.
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.


The proper way to query all entities belonging to a given set is to JOIN your tables and use a WHERE clause to limit the set to the given set.


  …given 4 collections, when I select all the entities in set 1, I want
  to get (collection 1 INTERSECT collection 2 EXCEPT collection 3)


This statement does not make sense.  Either you are selecting all entities in set 1 or you are not.  If you are only selecting collection 1 INTERSECT 2 EXCEPT 3, then you are not selecting all entities in set 1.  You can select all entities in set one that are also in collection 1 INTERSECT 2 EXCEPT 3, let us know if this is what you meant.

It looks like you have different patterns of ways you would like to transform the data.  In Oracle, if you give each pattern a function returning a pipelined result set, you can then pass the appropriate collection identifiers to the appropriate pattern function and have it return the requested results.  So, for you first example you could call:

SELECT * FROM TABLE(MyPatterns.Pattern1(1,2,3);


The Pattern1 function would do the x INTERSECT y EXCEPT z and return the results.