Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

enter image description here

share|improve this question
    
To be clear, I know how to perform all of the necessary set operations using SQL, what I need is a way to store these operations as metadata related to each set and then use that metadata to perform the correct set operations at runtime. Each set will be constructed using a different set of operators, so I need something akin to an expression tree or little languge in SQL. Thanks! –  Brad Urani Dec 21 '12 at 23:34
    
Hmmmm, what about using a host language that can construct the SQL expressions from meta data; then you call the functions? That way, the functions implement a DSL layer for the set operations? –  RonaldBarzell Dec 21 '12 at 23:44
add comment

migrated from stackoverflow.com Dec 22 '12 at 5:21

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 0 down vote accepted

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.

share|improve this answer
    
The approach we ended up trying was a more complicated version of this that allowed us to represent nested (parenthetical) expressions using table relationships. In the end though, it was too slow and we resorted to programmatically creating a new view for every set which gave us the desired performance and kept everything simple –  Brad Urani Feb 27 '13 at 16:13
    
My thinking is that your performance issues are due to trying to do intersections and unions. It might be better to look at the operations from a relational perspective instead of a set perspective, pushing select, join, etc to the extent you can and avoiding intersect, exclude, and union where you can. –  Chris Travers Feb 28 '13 at 0:17
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.