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.

The goal is to construct a hierarchical database of Objects having the following format:

TypeA -> ObjectA [values, contains ObjectB*, ObjectC*, etc.]
TypeB -> ObjectB [values, contains ObjectC*, ObjectD*, etc.]
TypeC -> ObjectC [values, ...]
TypeD -> ObjectD [values, ...]
TypeE -> ObjectE [values, ...]

edit: Each type can contain multiple objects of other types. eg. ObjectA can contain Multiple typeB, typeC. etc.

It is fixed and known what kind of values and objects each Type[A,B,C,...] may contain. Select or searches can be on on all objects of a certain type, and the system would construct an object containing all the sub-objects referenced by that object. Example: select all objects of TypeA -> returns :

ObjectA {
  values,
  ObjectB { values, ObjectC, ObjectD, etc. }
  ObjectC { values, ... }
  ...
 }

Doing this would mean a large number of nested select queries on the table. This being an embedded database, it is needed that the number of queries be minimum and there be no large joins, is there any way to partition the table or create indexes or a design principle that would help in optimizing the queries?

share|improve this question
    
An object of type A contains exactly one object of each type, i.e. one of type B, one of type C one of D and one of E? Or maximum one of each type? Or possibly many objects of each type? –  ypercube Mar 21 at 15:46
    
Can an object of type A and an object of type B contain the same object of type C? –  ypercube Mar 21 at 15:47
    
ObjectsA can contain multiple types of other objects. ObjectA and ObjectB can contain the same objectC. –  kicker86 Mar 21 at 15:53
    
if there is a solution without ObjectA and ObjectB having the same objectC, that would be acceptable as well. –  kicker86 Mar 21 at 15:55
    
Multiples types ok. Multiple items of the same type? –  ypercube Mar 21 at 15:55

1 Answer 1

With the restrictions you have supplied and if you want to implement this is DBMS, I think you could use a supertype/subtype pattern for the 5 (or more) types of objects and only one additional table for the "object contains objects" list:

-- auxiliary table that has only 5 rows, one for each type
CREATE TABLE types
( level TINYINT NOT NULL PRIMARY KEY
, type_name VARCHAR(10) NOT NULL UNIQUE
) ;

INSERT INTO types (level, type_name)
VALUES (1,'A'), (2,'B'), (3,'C'), (4,'D'), (5,'E') ; 

-- this is the supertype
CREATE TABLE objects
( object_id INT NOT NULL UNIQUE
, level TINYINT NOT NULL REFERENCES types (type_id)
, UNIQUE (level, object_id)
) ;

-- and the subtypes
CREATE TABLE a
( object_id INT NOT NULL PRIMARY KEY 
, level TINYINT NOT NULL CHECK (level = 1)
--- various columns
, FOREIGN KEY (level, object_id) 
    REFERENCES objects (level, object_id)
) ;

--- and similarly for the other 4 tables
--- all referencing objects table

and finally the list table, which is simple many to many junction table with only an additional constraint to ensure that an object of higher level can contain only objects of lower levels:

CREATE TABLE contains
( container_level TINYINT NOT NULL
, container_id INT NOT NULL 
, item_level TINYINT NOT NULL
, item_id INT NOT NULL 
, PRIMARY KEY (container_id, item_id)
, FOREIGN KEY (container_level, container_id) 
    REFERENCES objects (level, object_id)
, FOREIGN KEY (item_level, item_id) 
    REFERENCES objects (level, object_id)
, CHECK (container_level > item_level)
) ;

SQLite has CHECK constraints and has recently implemented recursive CTEs, if you want to get the results of an object and all the objects underneath it in one query.

From a fast search, H2 also supports recursive CTEs but Derby does not.

share|improve this answer

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.