What's the best practice for representing set operations in a relational database?

Question

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.

Answer 1

it sounds to me like you are trying to store in the database what are essentially conditions on which set operations are performed. This would seem to look more and more like an extended catalog for an in-db orm. The problem isn't just storing the set operations but also when to use them. I would do it like this (essentially storing this as a sort of graph):

CREATE TABLE set_catalog_class (
    id int not null unique,
    class_name text primary key
);

CREATE TABLE set_catalog_operation (
    id int not null unique,
    op_name text primary key
);

CREATE TABLE set_catalog_class_op (
    class_id int not null references set_catalog_class(id),
    op_id int not null references set_catalog_operation(id),
    relation_name text not null,
    order_num int not null,
    primary key (class_id, order_num)
);

This essentially gives you a very simple graph between nodes (operations) and ties them to relations which could then be views or the like.

Answer 2

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.