From the nested sets reference document written by Mike Hyller and other blogs, I could understand how hierarchies are being managed in RDBMS. I was also able to successfully implement the model for one of my projects. I am currently working on a problem which also has hierarchy, but the nodes are built from the bottom. I am using MySQL.
Consider I have 10 objects, I initially create rows for them in a table. Then, there is a table which has the left and right values that are required for implementing the nested sets model. So in this table, I group these 10 objects into two sets, say two bags, 5 objects in one bag and other 5 objects in one bag (based on some logic). Now these two bags are grouped together to form a bigger bag. Likewise, such bags are grouped together to form a big container.
I hope the example is clear to you to get an idea of what I am trying to achieve here. This is the opposite of applying the traditional nested set model where I build the sets from the top.
Can you please suggest me whether nested sets can be applied here? If yes, will changing the update query during insertion be sufficient to form the entire hierarchy? If you don't suggest, what other techniques can be used to tackle such problems?
Nested sets model works for any hierarchy, as long as it's non-overlapping (i.e. one child can have at most one parent).
Your model seems to have a predefined hierarchy ("objects", "bags" and "containers" being different entities with different properties). If it's the case indeed, you don't need nested sets at all, a simple set of foreign key constraints will suffice.
If it's not though (say, if a "bag" can be promoted to a "container", or there can be "containers" containing other "containers" etc.), you will need to have some kind of a hierarchy model indeed, and nested sets can serve as one as well.
One way to implement one would be to add references to you "bags" or "containers" or whatever to the table which holds your left and right values for your "objects":
CREATE TABLE nested_sets
(
ref BIGINT NOT NULL,
type INT NOT NULL -- 1 = object, 2 = set, 3 = bag
left BIGINT,
right BIGINT
)
INSERT
INTO nested_sets
VALUES (1, 1, 1, 1),
(2, 1, 2, 2),
(3, 1, 3, 3), -- 3 objects in bag 1
(4, 1, 4, 4),
(5, 1, 5, 5),
(6, 1, 6, 6), -- 3 objects in bag 2
(1, 2, 1, 3), -- bag 1, containing objects 1 to 3
(2, 2, 4, 6), -- bag 2, containing objects 4 to 6
(1, 3, 1, 6), -- container 1, containing bags 1 and 2 and, by extension, objects 1 to 6
You may also want to move left and right fields from the nested_sets table to the main tables describing the entities, or, alternatively, you may want to move all entities into a single table. This depends on how rigid your definitions of "bag", "container" and "object" are.